Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Last Updated: 2019-05-24   |   Comments (13)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > 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


Last Updated: 2019-05-24


get scripts

next tip button



About the author




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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 01, 2012 - 7:13:15 PM - SQL Server Helper Back To Top

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

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

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

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

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

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

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

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

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

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

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.


Learn more about SQL Server tools