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!