DATEPART is a T-SQL function that converts a DATE type to INT according to provided format.
datepart(datepart,date_time_value) is a T-SQL function available in MS SQL Server and the SAP/Sybase family of database products.
Questions tagged [datepart]
257 questions
2
votes
1 answer
Move SQL Server data in limited (1000 row) chunks to different tables on a monthly basis
I'm looking for a solution how I can move rows from a large table in chunks of 1000 to different tables based on parts of a datetime value (as a monthly archive). I'm using MS SQL Server 2008.
Remus Rusanu provided the following solution here on…
Sascha
- 21
- 2
2
votes
1 answer
Access DatePart function will not give the correct week number when used in a tabular form text box Control Source
I am attempting to create a tabular form in Access that lists the date and week number for each record. When I use the Format option in the textbox property sheet (ww), my weeks are off by 1 week (the week of Dec. 19, 2021 is considered week 52). I…
Bert
- 33
- 3
2
votes
3 answers
sql script to group hourly using datepart and fill the empty with 0
This script gives the hourly count of the timestamps in the table.
SELECT date_trunc('hour', s.fill_instant) h , count(date_trunc('hour', s.fill_instant)) c FROM sms s
left join station s2 on
s.station_id = s2.station_id
where
s2.address like…
Naveen Gopalakrishna
- 169
- 1
- 4
2
votes
3 answers
Copy prior month value and insert into new row
Here is an example of the current table I have:
1) Table name: TotalSales
Name Year Month Sales
------ ---- ----- -----
Alfred 2011 1 100
What I want to do is create a table like this, add a new row(Prior month sales):
2) Table name:…
AceAlfred
- 1,111
- 3
- 21
- 35
2
votes
1 answer
What is the best way to query the week number going into a new year?
I am trying to write a query that will get the weekly billing totals for the current week, 8 weeks before, and 8 weeks after. The query I have now works fine, however because the Week Number will be resetting with the new year, the data is falling…
user1457104
- 59
- 2
- 10
2
votes
1 answer
Is there a way to write and DateAdd and DatePart SSIS Expression to always generate ThisWeekMonday's date using Getdate()?
I have a package that needs to be run every Monday, because the file dates all have Monday's dates for each week that it is sent. If the package fails for some reason, I want to I to write it in such a way that anybody else can rerun it on any…
Sast77
- 97
- 1
- 9
2
votes
2 answers
Construct a date from parts in SQL Server 2008
I am using SQL Server 2008 and trying to form a query that will use a CASEstatement with a date calculation to return one of two dates depending on what day of the month the calculation results in.
The goal is if the calculation (in query below)…
XCCH004
- 321
- 1
- 11
2
votes
2 answers
DATEPART fix days
CASE WHEN DAY % 2 = 0 AND POL = 'SUUA'
THEN CONVERT(VARCHAR(15),DATEPART(DAY,5)) + ' TO ' + CONVERT(VARCHAR(15),DATEPART(DAY,3),103)
I set datefirst 1 but i can't how to improve this.
I'm sorry for less information. I'm using SQL Server.
I uploaded…
BRKZ
- 47
- 6
2
votes
3 answers
Exclude weekends from my query
I have a query which is working fine.
The query basically retrieves data between 6-8 days old from current date. I'd like exclude the weekends when measuring the age of the data.
Example: If a record is registered Friday, then Monday morning it will…
pancake
- 590
- 7
- 24
2
votes
0 answers
equivalent for trunc and to_date(to_char in conjunction
I hate so much when it come down to convertion dates from one database system to another. I understand the concept of trunk and to_date used in oracle but
From:
nFirstDayOfWeek := TO_NUMBER(TO_CHAR(TRUNC(nDate, 'MONTH'), 'D'));
To SQL Server I…
Joel Jacobson
- 145
- 3
- 15
2
votes
2 answers
Group weekly over several years (postgresql)
This query groups per week correctly, but sorts strange:
SELECT date_part('week' ,date), SUM(qty)
FROM hr
WHERE date between '2016-01-01' and '2016-01-31'
GROUP BY date_part('week', date)
ORDER BY 1
The result of this query is:
It…
sibert
- 1,968
- 8
- 33
- 57
2
votes
4 answers
SQL query to select range from specific date/time yesterday to specific date/time current day
SELECT *
FROM services
WHERE service_date BETWEEN (dateadd(DD, -1, getdate())) AND (dateadd(DD, 1, getdate())) **---Gives us a last one day data.**
but what we really need is data from 07:00 AM yesterday to 06:59 AM current day, so I tried the…
Altimo
- 51
- 3
2
votes
1 answer
Datepart function error in Microsoft Dynamics SQL
I am currently trying to perform a SQL query in Microsoft Dynamics AX2012, to output a year from delivery date by using a DATEPART function.
I have created a class "CustRerportDemo" in the AOT, and while attempting to perform a query, which is to…
developer
- 61
- 2
- 10
2
votes
2 answers
How to use DATEPART of the GETDATE as a variable for a query
This might be easier than I'm making it, I'm pretty new to SQL.
I have a database with a lot of movies release dates in the format YYYY-MM-DD.
I'm trying to write a query that would return films released on the current date (MM-DD) in various…
SonOfJorEl
- 23
- 2
2
votes
2 answers
SQL display months when Count is 0
I was wondering if someone could help me...
I have the following SQL query (have shortened this down as its a large union query)
SELECT [ Month ],
sum(total)
from
(select datename(month,Resolved1Date) as ' Month ',
COUNT(case when…
sql2015
- 591
- 3
- 13
- 34