SQL Server DATENAME Function


By:

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
DATENAME simple example

NULL Values with DATENAME

If a null parameter is used, the function will return a null value.

SELECT DATENAME(month, NULL) as datename
datename null values

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 and a table

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
DATENAME units used

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 qq 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
CONTACT datename

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


Last Update: 1/14/2022




Comments For This Article





download














get free sql tips
agree to terms