T-SQL DATEPART Explained
Here is a quick rundown on the T-SQL DATEPART function for SQL Server. DATEPART is used to pull a single part of a date/time element out as shown below.
The following query uses DATEPART to extract the year from the datetime input.
SELECT DATEPART(year, 'April 19, 2013') as year;
The following query uses DATEPART to extract the quarter from the datetime input.
SELECT DATEPART(quarter, 'April 19, 2013') as quarter;
The following query uses DATEPART to extract the month part from the datetime input.
SELECT DATEPART(month, 'April 19, 2013') as month ;
The following query uses DATEPART to extract the dayofyear part from the datetime input.
SELECT DATEPART(dayofyear, 'April 19, 2013') as dayofyear ;
The following query uses DATEPART to extract the day part from the datetime input.
SELECT DATEPART(day, 'April 19, 2013') as day ;
The following query uses DATEPART to extract the week part from the datetime input.
SELECT DATEPART(week, 'April 19, 2013') as week ;
The following query uses DATEPART to extract the weekday part from the datetime input.
SELECT DATEPART(weekday, 'April 19, 2013') as weekday ;
The following query uses DATEPART to extract the hour part from the datetime input.
SELECT DATEPART(hour, 'April 19, 2013 09:01:22.123') as hour ;
The following query uses DATEPART to extract the minute part from the datetime input.
SELECT DATEPART(minute, 'April 19, 2013 09:01:22.123') as minute ;
The following query uses DATEPART to extract the second part from the datetime input.
SELECT DATEPART(second, 'April 19, 2013 09:01:22.123') as second ;
The following query uses DATEPART to extract the millisecond part from the datetime input.
SELECT DATEPART(millisecond, 'April 19, 2013 09:01:22.123') as millisecond ;
Abbreviated format for DATEPART
The following query uses DATEPART to extract the Year part from the datetime input.
SELECT DATEPART(Yy, 'April 19, 2013') as Yy;
The following query uses DATEPART to extract the Quarter part from the datetime input.
SELECT DATEPART(Qq, 'April 19, 2013') as Qq;
The following query uses DATEPART to extract the Month part from the datetime input.
SELECT DATEPART(Mm, 'April 19, 2013') as Mm;
The following query uses DATEPART to extract the Day of the Year part from the datetime input.
SELECT DATEPART(Dy, 'April 19, 2013') as Dy;
The following query uses DATEPART to extract the Day of the Month part from the datetime input.
SELECT DATEPART(Dd, 'April 19, 2013') as Dd;
The following query uses DATEPART to extract the Week part from the datetime input.
SELECT DATEPART(Wk, 'April 19, 2013') as Wk;
The following query uses DATEPART to extract the Day of the Week part from the datetime input.
SELECT DATEPART(Dw, 'April 19, 2013') as Dw;
The following query uses DATEPART to extract the Hour part from the datetime input.
SELECT DATEPART(Hh, 'April 19, 2013 09:01:22.123') as Hh;
The following query uses DATEPART to extract the Minute part from the datetime input.
SELECT DATEPART(Mi, 'April 19, 2013 09:01:22.123') as Mi;
The following query uses DATEPART to extract the Second part from the datetime input.
SELECT DATEPART(Ss, 'April 19, 2013 09:01:22.123') as Ss;
The following query uses DATEPART to extract the Millisecond part from the datetime input.
SELECT DATEPART(Ms, 'April 19, 2013 09:01:22.123') as Ms;
Zero Padding
Note, the DatePart function returns an INT data type. If you need a zero padded return you can cast it to a VARCHAR, concatenate a 0 to the front, then use the RIGHT function to trim off extra padding, for instance the following:
SELECT RIGHT ('0' + CAST(DATEPART(Mi, 'April 19, 2013 09:01:22.123') AS VARCHAR(2)),2) as Mi;
There are many great ways to use the DatePart, these are just some of them.
Enjoy.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
It might be interesting to note that DatePart returns an Int data type. This can be relevant when someone wants to see a 2 digit (zero padded) day, hour, minute, second, etc…
George –
Thanks for the comment. Based on your suggestion I updated the DatePart post to include an example that will allow for a 2 digit (zero padded) result.
-Steve Stedman
Hi,
don’t use the week numbering if not using US week numbering System. It is wrong. No ISO.
As usual – the ISO week number as used within Europe – does not function correctly
If you look at the 3. January 1993 – which is a good example – The week number according to ISO (no fractional weeks on year-change, week starts with monday, the part of the week, which defines the week-number on year-change is the part which contains 4 days)
ISO for 3. January 1993 – is 53 (the last week of the previous year)
US week no is 2
Even if you use “set language german”, the week-number stays wrong …
regards Klaus
Germany