How to Use the DATENAME() Function in SQL Server: A Detailed Guide

Syntax:

DateName(DatePart, Date)

DateName(DatePart, Date) - Returns a string, that represents a part of the given date. This functions takes 2 parameters. The first parameter 'DatePart' specifies, the part of the date, we want. The second parameter, is the actual date, from which we want the part of the Date.

Code:

Select DATENAME(Day, '2012-09-30 12:43:46.837') -- Returns 30
Select DATENAME(WEEKDAY, '2012-09-30 12:43:46.837') -- Returns Sunday
Select DATENAME(MONTH, '2012-09-30 12:43:46.837') -- Returns September

Example:

Select Name, DateOfBirth, DateName(WEEKDAY,DateOfBirth) as [Day], 
            Month(DateOfBirth) as MonthNumber, 
            DateName(MONTH, DateOfBirth) as [MonthName],
            Year(DateOfBirth) as [Year]
From   tblEmployees

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);

Output:

The above code will produce the following result-