SQL DATENAME() is a SQL Server function used to return the name or value of the specified datepart of the a date.
SQL DATENAME Function Syntax
DATENAME(datepart, date-expression)
datepart specifies the part of the date to return.
| Datepart | Abbreviations | Description and Examples |
|---|---|---|
| year | yy,yyyy | number representing the year, like 2011 |
| quarter | qq,q | number representing the quarter, 1-4 1: January – March, 2: April – June, 3: July – September, 4: October – December |
| month | mm,m | name of the month, January – December |
| dayofyear | dy, y | total day from beginning of the year, 1-366 |
| day | dd, d | Day of month (1 – 31) |
| week | wk, ww | Week number (1 – 53) |
| weekday | dw | Day of week in characters (Monday, Tuesday, … Sunday) |
| hour | hh | Hour number of the day with 24 hour format (0 – 23) |
| minute | mi, n | Minute number (0 – 59) |
| second | ss, s | Second number (0 – 59) |
| millisecond | ms | Millisecond number |
SQL DATENAME Function Example
SELECT GETDATE() -- It will return: 2011-11-26 15:50:08.577
SELECT DATENAME(year, GETDATE()) SELECT DATENAME(yyyy, GETDATE()) SELECT DATENAME(yy, GETDATE()) -- It will return: 2011
SELECT DATENAME(quarter, GETDATE()) SELECT DATENAME(qq, GETDATE()) SELECT DATENAME(q, GETDATE()) -- It will return: 4
SELECT DATENAME(month, GETDATE()) SELECT DATENAME(mm, GETDATE()) SELECT DATENAME(m, GETDATE()) -- It will return: November
SELECT DATENAME(dayofyear, GETDATE()) SELECT DATENAME(dy, GETDATE()) SELECT DATENAME(y, GETDATE()) -- It will return: 330
SELECT DATENAME(day, GETDATE()) SELECT DATENAME(dd, GETDATE()) SELECT DATENAME(d, GETDATE()) -- It will return: 26
SELECT DATENAME(week, GETDATE()) SELECT DATENAME(wk, GETDATE()) SELECT DATENAME(ww, GETDATE()) -- It will return: 48
SELECT DATENAME(weekday, GETDATE()) SELECT DATENAME(dw, GETDATE()) -- It will return: Saturday
SELECT DATENAME(hour, GETDATE()) SELECT DATENAME(hh, GETDATE()) -- It will return: 15
SELECT DATENAME(minute, GETDATE()) SELECT DATENAME(mi, GETDATE()) SELECT DATENAME(n, GETDATE()) -- It will return: 50
SELECT DATENAME(second, GETDATE()) SELECT DATENAME(ss, GETDATE()) SELECT DATENAME(s, GETDATE()) -- It will return: 8
SELECT DATENAME(millisecond, GETDATE()) SELECT DATENAME(ms, GETDATE()) -- It will return: 577