Examples
In the following example, each DATENAME returns 'Wednesday' because that is the day of week ('dw') of the specified date:
SELECT DATENAME('dw','2018-02-21') AS DayName,
DATENAME(dw,'02/21/2018') AS DayName,
DATENAME('DW',64700) AS DayName
The following example returns 'December' because that is the month name ('mm') of the specified date:
SELECT DATENAME('mm','2018-12-20 12:00:00') AS MonthName
The following example returns '2018' (as a string) because that is the year ('yy') of the specified date:
SELECT DATENAME('yy','2018-12-20 12:00:00') AS Year
Note that the above examples use the abbreviations of the date parts. However, you can specify the full name, as in this example:
SELECT DATENAME('year','2018-12-20 12:00:00') AS Year
The following example returns the current quarter, week-of-year, and day-of-year. Each value is returned as a string:
SELECT DATENAME('Q',$HOROLOG) AS Q,
DATENAME('WK',$HOROLOG) AS WkCnt,
DATENAME('DY',$HOROLOG) AS DayCnt
The following example passes in the datepart and date-expression as a host variables:
SELECT DATENAME("year",$HOROLOG)
The following example uses a subquery to returns records from Sample.Person whose day of birth was a Wednesday:
SELECT Name AS WednesdaysChild,DOB
FROM (SELECT Name,DOB,DATENAME('dw',DOB) AS Wkday FROM Sample.Person)
WHERE Wkday='Wednesday'
ORDER BY DOB
