DATENAME

Returns a character string that represents the specified datepart of the specified date.

Syntax

DATENAME ( datepart , date )

Parameters

datepart
The part of the date that is to be returned. This parameter can be any of the valid datepart arguments recognized by SQL Server. The following table lists all valid datepart arguments.
date
The expression that is returning a valid date, datetime, datetime2, smalldatetime, datetimeoffset, or time value.

Return Value

nvarchar

Valid Datepart Arguments

Datepart	Abbreviation(s)	Description
`year`	`yy`, `yyyy`	Returns the year.
`quarter`	`qq`, `q`	Returns the quarter of the year (1, 2, 3, or 4).
`month`	`mm`, `m`	Returns the month.
`dayofyear`	`dy`, `y`	Returns the day of the year.
`day`	`dd`, `d`	Returns the day of the month.
`week`	`wk`, `isowk`	Returns the week of the year. The numbering of weeks depends on the `DATEFIRST` setting.
`weekday`	`dw`, `w`	Returns the day of the week. The value returned corresponds to the `DATEFIRST` setting.
`hour`	`hh`, `h`	Returns the hour.
`minute`	`mi`, `n`	Returns the minute.
`second`	`ss`, `s`	Returns the second.
`millisecond`	`ms`	Returns the milliseconds.
`microsecond`	`mcs`	Returns the microseconds.
`nanosecond`	`ns`	Returns the nanoseconds.

Examples

Example 1: Get the month name from a date

SELECT DATENAME(month, '2017-08-20');
-- Returns 'August'

SELECT DATENAME(mm, GETDATE());
-- Returns the name of the current month.

Example 2: Get the day of the week name

SELECT DATENAME(weekday, '2017-08-20');
-- Returns 'Sunday' (assuming default DATEFIRST setting)

Example 3: Get the quarter name

SELECT DATENAME(quarter, '2017-08-20');
-- Returns '3'

SELECT DATENAME(qq, '2017-02-15');
-- Returns '1'

Remarks

DATENAME returns a varchar or nvarchar value. The data type returned depends on the input string. If you pass an ANSI string, DATENAME returns varchar. If you pass a Unicode string, DATENAME returns nvarchar.

The DATEFIRST setting affects the day of the week returned. By default, DATEFIRST is set to 7 (Sunday).

For a list of all supported date and time functions, see Date and Time Functions (Transact-SQL).

< Previous: DATEPART Next: SYSDATETIME >