T-SQL DATEPART Explained

Download PDF

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;

DATEPART_year

The following query uses DATEPART to extract the quarter from the datetime input.

SELECT DATEPART(quarter, 'April 19, 2013') as quarter;

DATEPART_quarter
The following query uses DATEPART to extract the month part from the datetime input.

SELECT DATEPART(month, 'April 19, 2013') as month ;

DATEPART_month
The following query uses DATEPART to extract the dayofyear part from the datetime input.

SELECT DATEPART(dayofyear, 'April 19, 2013') as dayofyear ;

DATEPART_DayOfYear
The following query uses DATEPART to extract the day part from the datetime input.

SELECT DATEPART(day, 'April 19, 2013') as day ;

DATEPART_Day
The following query uses DATEPART to extract the week part from the datetime input.

SELECT DATEPART(week, 'April 19, 2013') as week ;

DATEPART_week
The following query uses DATEPART to extract the weekday part from the datetime input.

SELECT DATEPART(weekday, 'April 19, 2013') as weekday ;

DATEPART_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 ;

DATEPART_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 ;

DATEPART_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 ;

DATEPART_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 ;

DATEPART_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;

DATEPART_Yy
The following query uses DATEPART to extract the Quarter part from the datetime input.

SELECT DATEPART(Qq, 'April 19, 2013') as Qq;

DATEPART_Qq
The following query uses DATEPART to extract the Month part from the datetime input.

SELECT DATEPART(Mm, 'April 19, 2013') as Mm;

DATEPART_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;

DATEPART_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;

DATEPART_Dd
The following query uses DATEPART to extract the Week part from the datetime input.

SELECT DATEPART(Wk, 'April 19, 2013') as Wk;

DATEPART_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;

DATEPART_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;

DATEPART_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;

DATEPART_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;

DATEPART_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;

DATEPART_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;

DATEPART_Leading0

There are many great ways to use the DatePart, these are just some of them.

Enjoy.

Tagged with: , , , , ,
3 comments on “T-SQL DATEPART Explained
  1. George Mastros says:

    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…

    • SteveStedman SteveStedman says:

      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

  2. Klaus Oberdalhoff says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.