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:
datepart | datepart abbreviation |
---|---|
year | y, yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy |
day | dd, d |
week | wk, ww |
hour | hh |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
TZoffset | tz |
ISO_WEEK | ISOWK, 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.
SELECT DATENAME(mm,GETDATE())AS ThisMonth
In the following example, different dateparts of a given date string are returned
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.
SELECT EmployeeId, DATENAME(yy, HireDate) AS HireYear FROM Employee