SQL Server DATENAME() Function

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 a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2, or DATETIMEOFFSET value.

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.