So many similarities
There are many similarities between
DATEPART returns the date or time part as an integer,
DATENAME returns the part as a character string.
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
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
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.