 |
|
Determine SQL Server Date and Time with DATEPART and DATENAME Functions
|
By: Tim Cullen | Read Comments (10) | Print Tim has been working in the IT industry since 2003 and currently works as a SQL Server Reports Developer. Related Tips: 1 | 2 | 3 | 4 | 5 | 6 | More |
|
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
- Next time you have a need determine hour, day of week, month, quarter, year, etc. as a number be sure to refer back to DatePart examples in this tip to get you started in the right direction.
- In addition, if you have a need determine week day, month, etc. as a character string be sure to refer back to DateName examples in this tip to get you started in the right direction.
- If you check out all of the options of the DatePart 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.
- Check out these related tips:
Last Update: 10/17/2011
| Share: |  |
Share | Tweet |
|
 |
 |
Free SQL Server Learning |
Comments and Feedback:
| Monday, October 17, 2011 - 9:33:51 AM - Usman Butt | Read The Tip |
|
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. |
|
| Monday, October 17, 2011 - 2:03:15 PM - Tim Cullen | Read The Tip |
|
Nice catch, Usman. I've requested the change and it should show up in a little while.
Tim |
|
| Monday, October 17, 2011 - 3:14:49 PM - Tim Cullen | Read The Tip |
|
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. |
|
| Wednesday, October 26, 2011 - 4:32:09 PM - Bob | Read The Tip |
|
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".
|
|
| Wednesday, October 26, 2011 - 5:23:57 PM - Greg Robidoux | Read The Tip |
|
Bob, does this part of the code work for you: DECLARE @StartDate DATE = '01/01/2011', @EndDate DATE = '12/31/2011'
|
|
| Thursday, October 27, 2011 - 5:07:21 PM - Bob | Read The Tip |
|
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.
|
|
| Thursday, October 27, 2011 - 5:10:38 PM - Greg Robidoux | Read The Tip |
|
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:43:16 PM - Tim Cullen | Read The Tip |
|
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? |
|
| Friday, October 28, 2011 - 12:21:24 PM - Bob | Read The Tip |
|
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 |
|
| Friday, October 28, 2011 - 1:53:17 PM - Tim Cullen | Read The Tip |
|
No problem at all, Bob. Thanks for reading the tips and working the information provided in them! |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |