The DATENAME function returns a string with the part specified in the function of the date used.
Explanation
Syntax
DATENAME(datepart, datetime)
Parameters
datepart – It is the part of the date that we to get. It can be a year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear(dy, y), day (dd, d), week (wk, ww), weekday (dw, w), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), TZoffset (tz), ISO_WEEK (ISOWK,ISOWW). You can use the full name or the abbreviations in parenthesis.
datetime – Is the datetime, date or time used in the function to return part of it.
Simple DATETIME Example
The following example will show the year of the datetime specified.
SELECT DATENAME(year, '2020-03-05 2:10:30.123') as datename
NULL Values with DATENAME
If a null parameter is used, the function will return a null value.
SELECT DATENAME(month, NULL) as datename
Conversion failed when converting date for DATENAME
A typical error is a conversion failure. The following code shows an example:
SELECT DATENAME(day,'2021, March 21')
The date is literal and a literal date is not supported. The error message displayed will be the following.
Conversion failed when converting date and/or time from character string.
DATENAME with a Table Data
The following example shows the year, month and day of birthdates of the employee table.
SELECT DATENAME(YY, BirthDate) as year, DATENAME(MM, BirthDate) as month, DATENAME(dd, BirthDate) as day
FROM HumanResources.Employee
DATENAME Example with Different Units
The following example shows the datename using different units.
SELECT
DATENAME(yy, '2020-03-05 2:10:30.123') as YEAR,
DATENAME(mm, '2020-03-05 2:10:30.123') as MONTH,
DATENAME(DD, '2020-03-05 2:10:30.123') as DAY,
DATENAME(hh, '2020-03-05 2:10:30.123') as HOUR,
DATENAME(mi, '2020-03-05 2:10:30.123') as MINUTE,
DATENAME(ss, '2020-03-05 2:10:30.123') as SECOND,
DATENAME(ms, '2020-03-05 2:10:30.123') as MILLISECOND
Here is another example where we use the date below and return all possible output.
