Summary: in this tutorial, you will learn how to use the SQL Server DATENAME()
function to get a character string that represents a specified date part of a date.
SQL Server DATENAME() function overview
The DATENAME()
function returns a string, NVARCHAR
type, that represents a specified date part e.g., year, month, and day of a specified date.
The following shows the syntax of the DATENAME()
function:
DATENAME(date_part,input_date)
Code language: SQL (Structured Query Language) (sql)
The DATENAME()
function accepts two arguments:
date_part
is a part of the date that you want to return. The table below lists all valid date part values.input_date
is a literal date or an expression that can resolve to aTIME
,DATE
,SMALLDATETIME
,DATETIME
,DATETIME2
, orDATETIMEOFFSET
value.
date_part | 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 |
DATENAME() vs. DATEPART()
Note that DATENAME()
is similar to the DATEPART()
except for the return type. The DATENAME()
function returns the date part as a character string whereas the DATEPART()
returns the date part as an integer.
See the following example:
SELECT
DATEPART(year, '2018-05-10') [datepart],
DATENAME(year, '2018-05-10') [datename];
Code language: SQL (Structured Query Language) (sql)
The output looks the same:
datepart datename ----------- ----------- 2018 2018 (1 row affected)
However, their data types are different as shown in the following example:
SELECT
DATEPART(year, '2018-05-10') + '1' [datepart],
DATENAME(year, '2018-05-10') + '1' [datename] ;
Code language: SQL (Structured Query Language) (sql)
The following shows the result:
datepart datename ----------- ----------- 2019 20181 (1 row affected)
Because the DATEPART()
function returns an integer, the expression evaluates to 2019 (2018
+ 1
). However, the DATENAME()
function returns a character string, therefore, the +
is the concatenation operator which results in '20181'
(2018
+ 1
).
SQL Server DATENAME() function example
This example uses the DATENAME()
function to return various date parts of the '2020-10-02 10:20:30.1234567 +08:10'
:
DECLARE @dt DATETIME2= '2020-10-02 10:20:30.1234567 +08:10';
SELECT 'year,yyy,yy' date_part,
DATENAME(year, @dt) result
UNION
SELECT 'quarter, qq, q',
DATENAME(quarter, @dt)
UNION
SELECT 'month, mm, m',
DATENAME(month, @dt)
UNION
SELECT 'dayofyear, dy, y',
DATENAME(dayofyear, @dt)
UNION
SELECT 'day, dd, d',
DATENAME(day, @dt)
UNION
SELECT 'week, wk, ww',
DATENAME(week, @dt)
UNION
SELECT 'weekday, dw, w',
DATENAME(weekday, @dt)
UNION
SELECT 'hour, hh' date_part,
DATENAME(hour, @dt)
UNION
SELECT 'minute, mi,n',
DATENAME(minute, @dt)
UNION
SELECT 'second, ss, s',
DATENAME(second, @dt)
UNION
SELECT 'millisecond, ms',
DATENAME(millisecond, @dt)
UNION
SELECT 'microsecond, mcs',
DATENAME(microsecond, @dt)
UNION
SELECT 'nanosecond, ns',
DATENAME(nanosecond, @dt)
UNION
SELECT 'TZoffset, tz',
DATENAME(tz, @dt)
UNION
SELECT 'ISO_WEEK, ISOWK, ISOWW',
DATENAME(ISO_WEEK, @dt);
Code language: SQL (Structured Query Language) (sql)
Here is the output:
date_part result ---------------------- ----------- day, dd, d 2 dayofyear, dy, y 276 hour, hh 10 ISO_WEEK, ISOWK, ISOWW 40 microsecond, mcs 123456 millisecond, ms 123 minute, mi,n 20 month, mm, m October nanosecond, ns 123456700 quarter, qq, q 4 second, ss, s 30 TZoffset, tz +00:00 week, wk, ww 40 weekday, dw, w 40 year,yyy,yy 2020 (15 rows affected)
In this tutorial, you have learned how to use the SQL Server DATENAME()
function to extract a date part as a character string from a date.