SQL DATENAME Function

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

The basic syntax of Sql Server Datename is as shown below

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 value 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 a given date (1 to 365)
day	dd, d	Display the day number from the given date (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	Display the Hour value inside the specified date
minute	mi, n	Display the Minute Value present in the specified 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 Datepart in Sql Server Datename will display the microsecond Value available in a given date
nanosecond	ns	This argument in sql datename function will display the Nanoseconds Value present in the given date
TZOffset	tz	This will display the Time Zone Offset Value present in a 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 in this SQL datename function. It can be column, expression or any variable.

SQL DATENAME 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

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

First, we declared one variable and assigned the date and time to that variable

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

SELECT 'YEAR', DATENAME(year, @Date)

We asked the SQL Datename function to display the Month name from the given date

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.

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

DATENAME(year, [HireDate]) AS [YEAR]

Below statement will display the Quarter value from the Hire Date Column

DATENAME(quarter, [HireDate]) AS [YEAR]