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.

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

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-

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	Friday
year,yyy,yy	2020