SQL DATENAME

SQL DATENAME 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, use this SQL 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

DatepartAbbreviationsDescription
yearyy,yyyyDisplay the year value from the given date
quarterqq, qDisplay the quarter value from the given date
monthmm, mDisplay the month name from the given date
dayofyeardy, yDisplay the day of a year number from a given date (1 to 365)
daydd, dDisplay the day number from the given date (1 to 31)
weekwk, wwThe SQL datename function with this argument display the Week number from the given date
weekdaydw, wDisplay the Week days name from the given date (Sunday to Saturday)
hourhhDisplay the Hour value inside the specified date
minutemi, nDisplay the Minute Value present in the specified date
secondss, sThis will display the Seconds Value present in the given date
millisecondmsThis will display the Milliseconds Value present in the given date
microsecondmcsThis Datepart in Sql Server Datename will display the microsecond Value available in a given date
nanosecondnsThis argument in sql datename function will display the Nanoseconds Value present in the date
TZOffsettzThis will display the Time Zone Offset Value.
ISO_WEEKisowk, isowwThis will display the Iso Week Number from the given date

Date: Please specify the valid date as second argument in this datename function. It can be column, expression or any variable.

SQL DATENAME Example 1

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

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

DECLARE @Date datetime2 = '2015-08-25 14:24:04.1234567'

The SQL Datename function will display the year value.

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

The Datename function prints the Month name

SELECT 'MONTH', DATENAME(month, @Date)

SQL DATENAME Function Example 2

In this example, we 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]
SQL DATENAME 2

The Datename function will print the Year value from the Hire Date Column

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

It will display the Quarter value from the Hire Date Column

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