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