Determine SQL Server Date and Time Parts with DATEPART and DATENAME Functions

By:   |   Comments (13)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dates


Problem

As with most applications and databases, our application retrieves data that has at least one date in the record. There are times when we need to return the name of day or retrieve the month or day from the data. What functions does SQL Server offer to assist in this area?  Check out this tip to learn about the SQL Server DatePart and DateName functions.

Solution

SQL Server offers two functions that help you with retrieving parts of a date: DATEPART and DATENAME. Both functions require two parameters: the unit of time and date to be queried against.

  • DATEPART functions returns an integer value
  • DATENAME function returns a string value - with the DATENAME function, the only units of time that return values different than the DATEPART function are the WEEKDAY and MONTH.

Also, the case of the DatePart and DateName arguments are not case senstive, so you can use either upper case or lower case.

SQL Server DatePart and SQL Server DateName Examples

Below are some examples using these functions which can be used in the WHERE, HAVING, GROUP BY and ORDER BY clauses.  The examples use data type datetime2, but you can also use the datetime data type but not get as much precision for some of the date parts.  Also, other date data types will work, but some of the datepart options will not work based on the date format.

SET NOCOUNT ON

DECLARE @Date datetime2
SET @Date = '2019-09-25  19:47:00.8631597'

SELECT DATEPART(ISO_WEEK,@Date)
SELECT DATEPART(TZoffset,@Date) -- not supported by datetime data type
SELECT DATEPART(NANOSECOND,@Date)
SELECT DATEPART(MICROSECOND,@Date)
SELECT DATEPART(MS,@Date)
SELECT DATEPART(SS,@Date)
SELECT DATEPART(MINUTE,@Date)
SELECT DATEPART(HH,@Date)
SELECT DATEPART(DW,@Date)
SELECT DATEPART(WEEK,@Date)
SELECT DATEPART(DAY,@Date)
SELECT DATEPART(DAYOFYEAR,@Date)
SELECT DATEPART(MM,@Date)
SELECT DATEPART(QUARTER,@Date)
SELECT DATEPART(YYYY,@Date)

SELECT DATENAME(ISO_WEEK,@Date)
SELECT DATENAME(TZoffset,@Date)
SELECT DATENAME(nanosecond,@Date)
SELECT DATENAME(microsecond,@Date)
SELECT DATENAME(millisecond,@Date)
SELECT DATENAME(ss,@Date)
SELECT DATENAME(minute,@Date)
SELECT DATENAME(HOUR,@Date)
SELECT DATENAME(weekday,@Date)
SELECT DATENAME(wk,@Date)
SELECT DATENAME(d,@Date)
SELECT DATENAME(dayofyear,@Date)
SELECT DATENAME(m,@Date)
SELECT DATENAME(quarter,@Date)
SELECT DATENAME(YYYY,@Date)

SET NOCOUNT OFF

Here is the output.

DATEPART ( @Date value used is '2019-09-25 19:47:00.8631597' )
Unit of time DatePart Arguments Query Result
ISO_WEEK isowk, isoww, ISO_WEEK SELECT DATEPART(ISO_WEEK,@Date) 39
TZoffset tz, TZoffset SELECT DATEPART(TZoffset,@Date) 0
NANOSECOND ns, nanosecond SELECT DATEPART(nanosecond,@Date) 863159700
MICROSECOND mcs, microsecond SELECT DATEPART(microsecond,@Date) 863159
MILLISECOND ms, millisecond SELECT DATEPART(millisecond,@Date) 863
SECOND ss, s, second SELECT DATEPART(ss,@Date) 0
MINUTE mi, n, minute SELECT DATEPART(minute,@Date) 47
HOUR hh, hour SELECT DATEPART(HOUR,@Date) 19
WEEKDAY dw, weekday SELECT DATEPART(weekday,@Date) 4
WEEK wk, ww, week SELECT DATEPART(wk,@Date) 39
DAY dd, d, day SELECT DATEPART(d,@Date) 25
DAYOFYEAR dy, y, dayofyear SELECT DATEPART(dayofyear,@Date) 268
MONTH mm, m. month SELECT DATEPART(m,@Date) 9
QUARTER qq, q, quarter SELECT DATEPART(quarter,@Date) 3
YEAR yy, yyyy, year SELECT DATEPART(YYYY,@Date) 2019
 
DATENAME ( @Date value used is '2019-09-25 19:47:00.8631597' )
Unit of time DateName Arguments Query Result
ISO_WEEK isowk, isoww, ISO_WEEK SELECT DATENAME(ISO_WEEK,@Date) 39
TZoffset tz, TZoffset SELECT DATENAME(TZoffset,@Date) +00:00
NANOSECOND ns, nanosecond SELECT DATENAME(nanosecond,@Date) 863159700
MICROSECOND mcs, microsecond SELECT DATENAME(microsecond,@Date) 863159
MILLISECOND ms, millisecond SELECT DATENAME(millisecond,@Date) 863
SECOND ss, s, second SELECT DATENAME(ss,@Date) 0
MINUTE mi, n, minute SELECT DATENAME(minute,@Date) 47
HOUR hh, hour SELECT DATENAME(HOUR,@Date) 19
WEEKDAY dw, weekday SELECT DATENAME(weekday,@Date) Wednesday
WEEK wk, ww, week SELECT DATENAME(wk,@Date) 39
DAY dd, d, day SELECT DATENAME(d,@Date) 25
DAYOFYEAR dy, y, dayofyear SELECT DATENAME(dayofyear,@Date) 268
MONTH mm, m. month SELECT DATENAME(m,@Date) September
QUARTER qq, q, quarter SELECT DATENAME(quarter,@Date) 3
YEAR yy, yyyy, year SELECT DATENAME(YYYY,@Date) 2019

Build a Calendar Date Part Table

One use for the DATEPART function is if you need to "profile" a calendar year into the various date parts and names. The script below creates a table variable and inserts the various date parts into the table variable:

SET NOCOUNT ON 

DECLARE @StartDate DATE = '01/01/2011', @EndDate DATE = '12/31/2011' 

DECLARE @Dates TABLE ( 
    CalendarDate DATE PRIMARY KEY
  , MonthNumber TINYINT
  , DateNumber TINYINT 
  , DateOfYear SMALLINT
  , WeekNumber TINYINT
  , DayOfWeekNumber TINYINT 
  , NameOfMonth VARCHAR(15)
  , NameOfDay VARCHAR(15) 
) 

WHILE DATEDIFF(DAY,@StartDate,@EndDate) >= 0 
BEGIN 
   INSERT INTO @Dates (CalendarDate, MonthNumber, DateNumber, DateOfYear, WeekNumber, DayOfWeekNumber , NameOfMonth, NameOfDay) 
   SELECT @StartDate
        , DATEPART(MONTH,@StartDate) 
        , DATEPART(DAY,@StartDate)
        , DATEPART(DAYOFYEAR,@StartDate) 
        , DATEPART(WEEK,@StartDate)
        , DATEPART(DW,@StartDate) 
        , DATENAME(MONTH,@StartDate)
        , DATENAME(DW,@StartDate) 
 
   SELECT @StartDate = DATEADD(DAY,1,@StartDate) 
   
END 

SELECT * FROM @Dates 

SET NOCOUNT OFF
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Cullen Tim Cullen has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Monday, January 29, 2018 - 9:27:49 AM - Greg Robidoux Back To Top (75053)

Hi Jitendra, not really the right place for this question, but try this instead:

select count(*) from tablename where columname is null


Monday, January 29, 2018 - 5:25:04 AM - Jitendra Mishra Back To Top (75051)

Hello Team,

I wanted to show count of NULL but in query it is always returning 0.

I am wirting "select count(Columname) from tablename where columname is null"

Thanks for help.

Regards,

Jitendra


Sunday, July 1, 2012 - 7:13:15 PM - SQL Server Helper Back To Top (18285)

Here's a link that shows different examples of the uses of the DATENAME date function:

http://www.sql-server-helper.com/tips/tip-of-the-day.aspx?tkey=03399ca7-ecd7-437c-a9ec-6c3a379ec5c1&tid=63&tkey=uses-of-the-datename-date-function


Friday, October 28, 2011 - 1:53:17 PM - Tim Cullen Back To Top (14956)

No problem at all, Bob.  Thanks for reading the tips and working the information provided in them!


Friday, October 28, 2011 - 12:21:24 PM - Bob Back To Top (14955)

Greg's solution ended the first issue but to answer Tim's no I'm going against a SQL 2K5 database.  Let me check against a 2K8 R2 database.....

Darn that was my problem.  Didn't sound bad at all Tim.  You were spot on what the issue was. :-)

I must have missed the tip identifyer for the version.

Sorry for the wandering distraction guys. 

But I've learned something and I appreciate your script and time to listen to the lost DBA...

Thank you!

Bob


Thursday, October 27, 2011 - 5:43:16 PM - Tim Cullen Back To Top (14943)

I know this sounds bad, but you mentioned above "I ran the code from the article as is and noted the following. I was using SQL 2K8 R2 SSMS. Just an FYI".  I see you were using SQL 2K8 R2 SSMS, but were you connected with a SQL Server 2008 instance?


Thursday, October 27, 2011 - 5:10:38 PM - Greg Robidoux Back To Top (14942)

That's weird that doesn't work for you.  Try this instead:

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = '01/01/2011'

SET @EndDate = '12/31/2011'


Thursday, October 27, 2011 - 5:07:21 PM - Bob Back To Top (14941)

Nope I think that's the part that doesn't work.  (Sorry about the delay and thanks for the response.)

Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.

 


Wednesday, October 26, 2011 - 5:23:57 PM - Greg Robidoux Back To Top (14933)

Bob, does this part of the code work for you:

DECLARE @StartDate DATE = '01/01/2011', @EndDate DATE = '12/31/2011'

 


Wednesday, October 26, 2011 - 4:32:09 PM - Bob Back To Top (14932)

I ran the code from the article as is and noted the following.  I was using SQL 2K8 R2 SSMS.  Just an FYI

Msg 139, Level 15, State 1, Line 0

Cannot assign a default value to a local variable.

Msg 137, Level 15, State 2, Line 8

Must declare the scalar variable "@StartDate".

Msg 137, Level 15, State 2, Line 14

Must declare the scalar variable "@StartDate".

Msg 137, Level 15, State 2, Line 18

Must declare the scalar variable "@StartDate".

 


Monday, October 17, 2011 - 3:14:49 PM - Tim Cullen Back To Top (14857)

And just to let everyone know-when you execute the DATENAME for the units of time that are normally integers, the integer will still be returned.


Monday, October 17, 2011 - 2:03:15 PM - Tim Cullen Back To Top (14853)

Nice catch, Usman.  I've requested the change and it should show up in a little while.

 

Tim


Monday, October 17, 2011 - 9:33:51 AM - Usman Butt Back To Top (14852)

Hi Tim,

Can you please elaborate the following point in your NEXT STEPS part

If you check out all of the options of the DateName function, you can pass in the hour, day of week, month, quarter, year, etc. parameters and will be returned numeric results as opposed to a character string.

I think you wanted to say DATEPART instead of DATENAME OR may be both DATEPART and DATENAME. Thanks.















get free sql tips
agree to terms