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.

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	isowk, isoww

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 (click on the image to enlarge):

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 favourite date or time part with me on Twitter at @bornsql.