SQL DATENAME is one of the Date function is used to extract or display specified date part from the existing date. This function always return String data. For example, If you want to extract month name or day name (sunday .. saturday) from the existing Date, you can use this SQL Datename function.
SQL DATENAME Syntax
|
1 |
DATENAME (Datepart, Date) |
Datepart: It is the part of a given date which we are going to display as output. 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 number from the given date |
| month | mm, m | Display the month name from the given date |
| dayofyear | dy, y | Display the day of a year number from the given date (1 to 365) |
| day | dd, d | Display the day number from the given date (1 to 31) |
| week | wk, ww | 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 | Display the Hour value present in the given date |
| minute | mi, n | Display the Minute Value present in the given date |
| second | ss, s | This will display the Seconds Value present in the given date |
| millisecond | ms | This will display the Milliseconds Value present in the given date |
| microsecond | mcs | This will display the microsecond Value present in the given date |
| nanosecond | ns | This will display the Nanoseconds Value present in the given date |
| TZOffset | tz | This will display the Time Zone Offset Value present in the given date |
| ISO_WEEK | isowk, isoww | This will display the Iso Week Number from the given date |
Date: Please specify the valid date as second argument. It can be column, expression or any variable.
SQL DATENAME Function Example 1
In this 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 datepart operations in SQL
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567' SELECT 'YEAR' AS [SQL DATENAME], 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) |
OUTPUT
ANALYSIS
We declared one variable and assigned the date and time to that variable
|
1 |
DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567' |
We asked the SQL Datename function to display the year value from the given date
|
1 |
SELECT 'YEAR', DATENAME(year, @Date) |
We asked the SQL Datename function to display the Month name from the given date
|
1 |
SELECT 'MONTH', DATENAME(month, @Date) |
SQL DATENAME Function Example 2
In this example, we are going to use one of the custom SQL table to perform SQL Datename operations on Hire Date column.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 |
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 [SQL Tutorial].[dbo].[Employee] |
OUTPUT
ANALYSIS
SQL Datename function in the below statement will display the Year value from the Hire Date Column
|
1 |
DATENAME(year, [HireDate]) AS [YEAR] |
Below statement will display the Quarter value from the Hire Date Column
|
1 |
DATENAME(quarter, [HireDate]) AS [YEAR] |
Thank You for Visiting Our Blog
Leave a Reply