Overview
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.
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 exampleThe error message displayed is the following.
Conversion failed when converting the varchar value 'The year is: ' to data type int.Additional Information
- 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
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.
- MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018
