By: Daniel Calbimonte
The DATENAME function returns a string with the part specified in the function of the date used.
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.
DECLARE @date datetime2 = '2021-01-07 14:36:17.6222691'
| DateGroup | DatePart | Query | Result |
|---|---|---|---|
| day | d | SELECT DATENAME(d, @date) | 7 |
| day | day | SELECT DATENAME(day, @date) | 7 |
| day | dd | SELECT DATENAME(dd, @date) | 7 |
| dayofyear | dayofyear | SELECT DATENAME(dayofyear, @date) | 7 |
| dayofyear | dy | SELECT DATENAME(dy, @date) | 7 |
| dayofyear | y | SELECT DATENAME(y, @date) | 7 |
| hour | hh | SELECT DATENAME(hh, @date) | 14 |
| hour | hour | SELECT DATENAME(hour, @date) | 14 |
| microsecond | microsecond | SELECT DATENAME(microsecond, @date) | 622269 |
| microsecond | mcs | SELECT DATENAME(mcs, @date) | 622269 |
| millisecond | millisecond | SELECT DATENAME(millisecond, @date) | 622 |
| millisecond | ms | SELECT DATENAME(ms, @date) | 622 |
| minute | mi | SELECT DATENAME(mi, @date) | 36 |
| minute | minute | SELECT DATENAME(minute, @date) | 36 |
| minute | n | SELECT DATENAME(n, @date) | 36 |
| month | m | SELECT DATENAME(m, @date) | January |
| month | mm | SELECT DATENAME(mm, @date) | January |
| month | month | SELECT DATENAME(month, @date) | January |
| nanosecond | nanosecond | SELECT DATENAME(nanosecond, @date) | 622269100 |
| nanosecond | ns | SELECT DATENAME(ns, @date) | 622269100 |
| quarter | q | SELECT DATENAME(q, @date) | 1 |
| quarter | SELECT DATENAME(qq, @date) | 1 | |
| quarter | quarter | SELECT DATENAME(quarter, @date) | 1 |
| second | s | SELECT DATENAME(s, @date) | 17 |
| second | second | SELECT DATENAME(second, @date) | 17 |
| second | ss | SELECT DATENAME(ss, @date) | 17 |
| week | week | SELECT DATENAME(week, @date) | 2 |
| week | wk | SELECT DATENAME(wk, @date) | 2 |
| week | ww | SELECT DATENAME(ww, @date) | 2 |
| weekday | dw | SELECT DATENAME(dw, @date) | Thursday |
| weekday | w | SELECT DATENAME(w, @date) | Thursday |
| weekday | weekday | SELECT DATENAME(weekday, @date) | Thursday |
| year | year | SELECT DATENAME(year, @date) | 2021 |
| year | yy | SELECT DATENAME(yy, @date) | 2021 |
| year | yyyy | SELECT DATENAME(yyyy, @date) | 2021 |
| TZoffset | TZoffset | SELECT DATENAME(TZoffset, @date) | +00:00 |
| TZoffset | tz | SELECT DATENAME(tz, @date) | +00:00 |
| ISO_WEEK | ISO_WEEK | SELECT DATENAME(ISO_WEEK, @date) | 1 |
| ISO_WEEK | ISOWK | SELECT DATENAME(ISOWK, @date) | 1 |
| ISO_WEEK | ISOWW | SELECT DATENAME(ISOWW, @date) | 1 |
Difference Between DATENAME and DATEPART
The first example will work with DATENAME.
SELECT 'The year is: ' + DATENAME(yy, '2020-03-05') as example
However, this will fail with DATEPART.
SELECT 'The year is: ' + DATEPART(yy, '2020-03-05') as example
The error message displayed is the following.
Conversion failed when converting the varchar value 'The year is: ' to data type int.
Related Articles
- How to Get Current Date in SQL Server
- Mimic timestamp behavior of other database platforms to store last modified date
- SQL Convert Date to YYYYMMDD
- SQL Server DIFFERENCE Function
- SQL Server CONCAT Function
Last Update: 1/14/2022
