Tutorialsteacher

Follow Us

SQL Server DATENAME() Function

In SQL Server, the DATENAME() function returns a string that represents the specified datepart of a given date.

Syntax:

DATENAME(datepart, date)

Parameters

datepart: The specific part of the specified date for which the DATENAME() function will return a string, e.g. day, month, year, hours, seconds, etc. The following table lists all valid datepart values:

datepartdatepart abbreviation
yeary, yy, yyyy
quarterqq, q
monthmm, m
dayofyeardy
daydd, d
weekwk, ww
hourhh
minutemi, n
secondss, s
millisecondms
microsecondmcs
nanosecondns
TZoffsettz
ISO_WEEKISOWK, ISOWW

date: A date or character expression that can resolve to one of the following data types: date, datetime, datetime2, time, smalldatetime, or datetimeoffset.

A date can also be a string expression, a user-defined variable, or a table column.

Return Value

Returns the datepart of the given date as nvarchar.

If the date is smalldatetime and datepart is seconds, then DATENAME() returns 00.

If the date is a variable or a table column, and its data type does not include the specified datepart, then DATENAME() function returns an error 9810

Note: DATENAME() can be used in the following clauses of an SQL query: SELECT, WHERE, GROUP BY, HAVING, and ORDER BY.

Get Month Name

In the followin example, month part of current date is returned.

Example:
SELECT DATENAME(mm,GETDATE())AS ThisMonth

In the following example, different dateparts of a given date string are returned

Example:
SELECT DATENAME(mm, '04/20/2021 9:45:52.564722') AS Month
,DATENAME(d, '04/20/2021 9:45:52.564722') AS Day
,DATENAME(yy, '04/20/2021 9:45:52.564722') AS Year
,DATENAME(hh, '04/20/2021 9:45:52.564722') AS Hour
,DATENAME(minute, '04/20/2021 9:45:52.564722') AS Minute
,DATENAME(s, '04/20/2021 9:45:52.564722') AS Second
,DATENAME(ms, '04/20/2021 9:45:52.564722') AS MilliSecond
,DATENAME(mcs, '04/20/2021 9:45:52.564722') AS MicroSecond
,DATENAME(q, '04/20/2021 9:45:52.564722') AS Quarter
,DATENAME(dayofyear, '04/20/2021 9:45:52.564722') AS DayOfYear
,DATENAME(wk, '04/20/2021 9:45:52.564722') AS Week

Use DATENAME() with Columns

The following uses the DATENAME() function with the DateTime type column of a table that returns a year as a string.

Example: DATENAME() with Column
SELECT EmployeeId, DATENAME(yy, HireDate) AS HireYear FROM Employee