Learn more about SQL Server tools

mssqltips logo
 

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

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Determine SQL Server Date and Time with DATEPART and DATENAME Functions


By:   |   Last Updated: 2011-10-17   |   Comments (13)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | 7 | More > Dates

Problem

As with most applications and databases nowadays, 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. The DATEPART functions returns an integer value, whereas the 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. Below are some examples using these functions which can be used in WHERE, HAVING, GROUP BY and ORDER BY clauses.

SQL Server DatePart and SQL Server DateName Examples

 
DATEPART (The @Date value used is '2011-09-25 19:47:00.8631597')
Unit of time Abbreviations Query Result
ISO_WEEK isowk, isoww SELECT DATEPART(ISO_WEEK,@Date) 38


TZoffset tz SELECT DATEPART(TZoffset,@Date) 0


NANOSECOND ns SELECT DATEPART(NANOSECOND,@Date) 863159700


MICROSECOND mcs SELECT DATEPART(MICROSECOND,@Date) 863159


MILLISECOND ms SELECT DATEPART(MS,@Date) 863


SECOND ss, s SELECT DATEPART(SS,@Date) 0


MINUTE mi, n SELECT DATEPART(MINUTE,@Date) 47


HOUR hh SELECT DATEPART(HH,@Date) 19


WEEKDAY dw SELECT DATEPART(DW,@Date) 1


WEEK wk, ww SELECT DATEPART(WEEK,@Date) 40


DAY dd, d SELECT DATEPART(DAY,@Date) 25


DAYOFYEAR dy, y SELECT DATEPART(DAYOFYEAR,@Date) 268


MONTH mm, m SELECT DATEPART(MM,@Date) 9


QUARTER qq, q SELECT DATEPART(QUARTER,@Date) 3


YEAR yy, yyyy SELECT DATEPART(YYYY,@Date) 2011

DATENAME (The @Date value used is '2011-09-25 19:47:00.8631597')
Unit of time Abbreviations Query Result
WEEKDAY dw SELECT DATENAME(WEEKDAY,@Date) Sunday


MONTH mm, m SELECT DATENAME(MM,@Date) September


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) 
 CONTINUE 
END 
SELECT * FROM @Dates 
SET NOCOUNT OFF
Next Steps


Last Updated: 2011-10-17


next webcast button


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