SQL DATENAME function is used to extract or display specified date part from the existing date. This SQL DATENAME function always returns String data. For example, If you want to extract the month name or day name (sunday .. saturday) from the existing Date, use this Datename function.
SQL DATENAME Syntax
The basic syntax of Sql Server Datename
DATENAME (Datepart, Date)
Datepart: It is the part of a given date which we are going to display as output. The following table will display the list of available datepart argument in SQL Server
| Datepart | Abbreviations | Description |
|---|---|---|
| year | yy,yyyy | Display the year value from the given date |
| quarter | qq, q | Display the quarter value from the given date |
| month | mm, m | Display the month name from the given date |
| dayofyear | dy, y | The day of a year number from 1 to 365 |
| day | dd, d | Day number from 1 to 31 |
| week | wk, ww | The SQL datename function with this argument display the Week number from the given date |
| weekday | dw, w | Display the Week days name from the given date (Sunday to Saturday) |
| hour | hh | Hour value |
| minute | mi, n | Minute Value present in the specified date |
| second | ss, s | This will display the Seconds Value |
| millisecond | ms | This will display the Milliseconds Value |
| microsecond | mcs | This Datepart in Sql Server Datename will display the microsecond Value available |
| nanosecond | ns | This argument in sql datename function will display the Nanoseconds Value |
| TZOffset | tz | This will display the Time Zone Offset Value. |
| ISO_WEEK | isowk, isoww | This will display the Iso Week Number |
Date: Please specify the valid date as second argument in this datename function. It can be column, expression or any variable.
SQL DATENAME Example
In this Date function example we are going to declare a variable of datetime2 data type. Let us assign valid date to that variable and perform all the available SQL DATENAME operations.
First, we declared one variable and assigned the date and time to that variable. The first SQL Server Datename statement will display the year number and the following statement prints the month.
DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567' SELECT 'YEAR' AS [SQLDATENAME], DATENAME(year, @Date) AS [Values] UNION ALL SELECT 'QUARTER', DATENAME(quarter, @Date) UNION ALL SELECT 'MONTH', DATENAME(month, @Date) UNION ALL SELECT 'DAYOFYEAR', DATENAME(dayofyear, @Date) UNION ALL SELECT 'DAY', DATENAME(day, @Date) UNION ALL SELECT 'WEEK', DATENAME(week, @Date) UNION ALL SELECT 'WEEKDAY', DATENAME(weekday, @Date) UNION ALL SELECT 'HOUR', DATENAME (hour, @Date) UNION ALL SELECT 'MINUTE', DATENAME(minute, @Date) UNION ALL SELECT 'SECOND', DATENAME(second, @Date) UNION ALL SELECT 'MILLISECOND', DATENAME(millisecond, @Date) UNION ALL SELECT 'MICROSECOND', DATENAME(microsecond, @Date) UNION ALL SELECT 'NANOSECOND',DATENAME(nanosecond, @Date) UNION ALL SELECT 'ISO WEEK',DATENAME(ISO_WEEK, @Date)
DATENAME Function Example 2
In this example, we use one of the custom table to perform SQL Datename operations on HireDate column.
For the fifth line, the Datename function will print the Year value from the HireDate Column. And the sixth line will display the Quarter value from the HireDate Column
SELECT [FirstName] + ' '+ [LastName] AS [Full Name]
,[Occupation]
,[YearlyIncome]
,[HireDate]
,DATENAME(year, [HireDate]) AS [YEAR]
,DATENAME(quarter, [HireDate]) AS [QUARTER]
,DATENAME(month, [HireDate]) AS [MONTH]
,DATENAME(day, [HireDate]) AS [DAY]
,DATENAME(dayofyear, [HireDate]) AS [TOTAL DAYS]
FROM [Employee]
