DATENAME Function - SQL Server Date Time Functions

DATENAME function returns a character string representing the specified datepart of the specified date. This function returns a nvarchar value. The return value depends on the language environment set by using "SET LANGUAGE".  DATENAME function can be used in the SELECT list, WHERE, GROUP BY, HAVING, ORDER BY.
Syntax

DATENAME ( datepart, date )

datepart	Specifies the part of the date that DATENAME will return
date	an expression that returns a date or datetime or datetimeoffset or datetime2 or smalldatetime or time or string in a valid date format value

The table below lists all valid datepart arguments

datepart	Abbreviations	Return Value
year	yy, yyyy	1840-9999
quarter	qq, q	1-4
month	mm, m	January,...December
dayofyear	dy, y	1-366
day	dd, d	1-31
week	wk, ww	1-53
weekday	dw, w	Sunday,..Saturday
hour	hh	0-23
minute	mi, n	0-59
second	ss, s	0-59
millisecond	ms	0-99(with precision of 2)
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	ISOWK, ISOWW	1-53

Examples

DECLARE @date DATETIME 
SET @date= '2019-08-27 16:28:42.013' 
 
SELECT @date AS CurrentDate;------------------------------2019-08-27 16:28:42.013

SELECT DATENAME(year,@date) AS 'Year';--------------------2019 
SELECT DATENAME(yy,@date) AS 'Year';----------------------2019 
SELECT DATENAME(yyyy,@date) AS 'Year';--------------------2019 

SELECT DATENAME(quarter,@date) AS 'Quarter';--------------3 
SELECT DATENAME(qq,@date) AS 'Quarter';-------------------3 
SELECT DATENAME(q,@date) AS 'Quarter';--------------------3 

SELECT DATENAME(month,@date) AS 'Month';------------------August 
SELECT DATENAME(mm,@date) AS 'Month';---------------------August
SELECT DATENAME(m,@date) AS 'Month';----------------------August 

SELECT DATENAME(dayofyear,@date) AS 'Dayofyear';----------239 
SELECT DATENAME(dy,@date) AS 'Dayofyear';-----------------239 
SELECT DATENAME(y,@date) AS 'Dayofyear';------------------239 

SELECT DATENAME(day,@date) AS 'Day';----------------------27 
SELECT DATENAME(dd,@date) AS 'Day';-----------------------27 
SELECT DATENAME(d,@date) AS 'Day';------------------------27 

SELECT DATENAME(week,@date) AS 'Week';--------------------35 
SELECT DATENAME(wk,@date) AS 'Week';----------------------35 
SELECT DATENAME(ww,@date) AS 'Week';----------------------35 

SELECT DATENAME(weekday,@date) AS 'Weekday';--------------Tuesday 
SELECT DATENAME(dw,@date) AS 'Weekday';-------------------Tuesday  
SELECT DATENAME(w,@date) AS 'Weekday';--------------------Tuesday  

SELECT DATENAME(hour,@date) AS 'Hour';--------------------16 
SELECT DATENAME(hh,@date) AS 'Hour';----------------------16 

SELECT DATENAME(minute,@date) AS 'Minute';----------------28 
SELECT DATENAME(mi,@date) AS 'Minute';--------------------28 
SELECT DATENAME(n,@date) AS 'Minute';---------------------28 

SELECT DATENAME(second,@date) AS 'Second';----------------42 
SELECT DATENAME(ss,@date) AS 'Second';--------------------42 
SELECT DATENAME(s,@date) AS 'Second';---------------------42 

SELECT DATENAME(millisecond,@date) AS 'Milli Second';-----13 
SELECT DATENAME(ms,@date) AS 'Milli Second';--------------13 

SELECT DATENAME(microsecond,@date) AS 'Micro Second';-----13333 
SELECT DATENAME(mcs,@date) AS 'Micro Second';-------------13333 

SELECT DATENAME(nanosecond,@date) AS 'Nano Second';-------13333333 
SELECT DATENAME(ns,@date) AS 'Nano Second';---------------13333333 

SELECT DATENAME(TZoffset,SYSDATETIMEOFFSET()) AS 'TZoffset';-- +05:30 
SELECT DATENAME(tz,SYSDATETIMEOFFSET()) AS 'TZoffset';-------- +05:30 

SELECT DATENAME(ISO_WEEK,@date) AS 'ISO_WEEK';-------------35
SELECT DATENAME(ISOWK,@date) AS 'ISO_WEEK';----------------35
SELECT DATENAME(ISOWW,@date) AS 'ISO_WEEK';----------------35