DATENAME function is used to get string of the specified
datepart of the specified date.
Syntax of DATENAME Function :
DATENAME ( datepart ,date )
Datepart specifies the part of
the date to return.
Date is
an expression that returns a datetime or smalldatetime value or a character string in a date
format.
Return type of DATENAME function is nvarchar.
Below
is the table that specifies possible datepart parameters, abbreviation of it
and return values with examples where current date is '08/02/2002'.
Date
Part | Abbreviation | Return
Values | Examples |
Year | yy | 1840-9999 | DATENAME(yy,GETDATE())
2002 |
Quarter | qq | 1-4 | DATENAME(qq,GETDATE())
3 |
Month | mm | January,...December | DATENAME(mm,GETDATE())
August |
Week | wk | 1-53 | DATENAME(wk,GETDATE()) 32 |
Weekday | dw | Sunday, Monday, Tuesday, Wednesday,
Thursday, Friday, Saturday | DATENAME(dw,GETDATE())
Monday |
Dayofyear | dy | 1-366 | DATENAME(dy,GETDATE()) 214 |
Day | dd | 1-31 | DATENAME(dd,GETDATE()) 2 |
Hour | hh | 0-23 | DATENAME(hh,GETDATE()) 9 |
Minute | mi | 0-59 | DATENAME(mi,GETDATE()) 49 |
Second |
| 0-59 | DATENAME(ss,GETDATE()) 5 |
millisecond | ms | 0-99 (with precision of 2) | DATENAME(ms,GETDATE()) 387 |
Example
of DATENAME Function :
Example
1 : Use of DATENAME function in SELECT clause
SELECT DATENAME(MONTH, '08/02/2010')
Output
August
Above
examples shows month name of 08/02/2010 (format : MM/DD/YYYY) date.
Example
2 : Use of DATENAME function in SELECT clause to display weekday of table field
SELECT ShipName, OrderDate, DATENAME(WEEKDAY,
OrderDate) AS 'WeekDay'
FROM Orders
Output
ShipName
OrderDate WeekDay
Vins et
alcools Chevalier 1996-07-04
00:00:00.000 Thursday
Toms
Spezialitäten 1996-07-05 00:00:00.000 Friday
Hanari
Carnes 1996-07-08
00:00:00.000 Monday
Victuailles
en stock 1996-07-08
00:00:00.000 Monday
Suprêmes
délices 1996-07-09 00:00:00.000 Tuesday
Above
example displays a week day of order date field.
Example
3 : Use of DATENAME function in WHERE clause
SELECT ShipName,
OrderDate
FROM Orders
WHERE DATENAME(QUARTER, OrderDate) = 2
Output
ShipName
OrderDate
Bottom-Dollar
Markets 1997-04-01
00:00:00.000
La
maison d'Asie
1997-04-02 00:00:00.000
Comércio
Mineiro 1997-04-02
00:00:00.000
Laughing
Bacchus Wine Cellars 1997-04-03
00:00:00.000
Tradiçao
Hipermercados 1997-04-04
00:00:00.000
Above
example displays all orders made in 2nd quarter of month.