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

By: Tim Cullen   |   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.

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

• Next time you have a need determine hour, day of week, month, quarter, year, etc. be sure to refer to the 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:
  • Date and Time Conversions Using SQL Server
  • SQL Server 2008 Date and Time Data Types
  • SQL Server function to convert integer date to datetime format
  • Add and Subtract Dates using DATEADD in SQL Server
  • SQL Server DateDiff Example

About the author

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