Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Dates and Times in SQL Server: DATENAME()

DZone's Guide to

Dates and Times in SQL Server: DATENAME()

Read here to learn more about dates and times in SQL server.

· Database Zone ·
Free Resource

Discover Tarantool's unique features which include powerful stored procedures, SQL support, smart cache, and the speed of 1 million ACID transactions on a single CPU core!

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:

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.

Discover Tarantool's unique features such as powerful stored procedures, SQL support, smart cache, and the speed of 1 million ACID transactions on a single CPU.

Topics:
database ,datepart ,datename ,sql ,sql server

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}