Dates and Times in SQL Server: DATENAME()

Last time, we looked at DATEPART(). This post is all about the DATENAME() function.

So Many Similarities

There are many similarities between DATEPART and DATENAME. Where DATEPART returns the date or time part as an integer, DATENAME returns the part as a character string.

This DATENAME function also takes two parameters: the date or time part we want back and the input date. Just as we saw with   DATEPART , the documentation indicates the input date parameter must be an "expression that can resolve to one of the following data types: date, smalldatetime, datetime, datetime2, datetimeoffset, or time."

Similarly, the date and time parts that can be returned look much like those in DATEPART, which gives us another opportunity for the reminder that we should avoid using the available abbreviations in order to help with writing clearly understandable code.

Settings and Formatting

The  LANGUAGE , DATEFORMAT , and DATEFIRST settings will affect the output, just as with DATEPART.

Speaking of date formatting (which as we've learned previously is easy to get wrong), the documentation addresses the Year-Day-Month format:

In SQL Server 2017, DATENAME implicitly casts string literals as a datetime2 type. In other words, DATENAME does not support the format YDM when the date is passed as a string. You must explicitly cast the string to a datetime or smalldatetime type to use the YDM format.

This is a good thing for consistency across regions. Enforcing the use of the DATETIME2 data type removes ambiguity.

Example code and output

Using the built-in function SYSUTCDATETIME() to get the current date and time in UTC format as a DATETIME2(7) data type, we can get the following output for each date or time part:

DECLARE @dt DATETIME2(7) = SYSUTCDATETIME();
 SELECT @dt AS [Current UTC Date and Time]SELECT DATENAME(YEAR, @dt) AS [Year];
SELECT DATENAME(QUARTER, @dt) AS [Quarter];
SELECT DATENAME(MONTH, @dt) AS [Month];
SELECT DATENAME(DAYOFYEAR, @dt) AS [Day Of Year];
SELECT DATENAME(DAY, @dt) AS [Day];
SELECT DATENAME(WEEK, @dt) AS [Week];
SELECT DATENAME(WEEKDAY, @dt) AS [Weekday];
SELECT DATENAME(HOUR, @dt) AS [Hour];
SELECT DATENAME(MINUTE, @dt) AS [Minute];
SELECT DATENAME(SECOND, @dt) AS [Second];
SELECT DATENAME(MILLISECOND, @dt) AS [Millisecond];
SELECT DATENAME(MICROSECOND, @dt) AS [Microsecond];
SELECT DATENAME(NANOSECOND, @dt) AS [Nanosecond];
SELECT DATENAME(TZOFFSET, @dt) AS [TZ Offset];
 SELECT DATENAME(ISO_WEEK, @dt) AS [ISO Week];

And here is the output:

Remember that all of these values are being returned as strings. We should keep this in mind if we want to manipulate the values.

Share your favorite date or time part with me in the comments.