SQL DATENAME 2

SQL DATENAME

SQL Server DATENAME function is used to extract or display specified date parts from the existing date. This 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.

DATENAME Syntax

The basic syntax of Sql Server Datename

DATENAME (Datepart, Date)

Datepart: It is the part of a given date that we are going to display as output. The following table will display the list of available datepart arguments 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, yThe day of a year number from 1 to 365
daydd, dDay number from 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)
hourhhHour value
minutemi, nMinute Value present in the specified date
secondss, sThis will display the Seconds Value
millisecondmsThis will display the Milliseconds Value
microsecondmcsThis Datename will display the microsecond Value available
nanosecondnsThis argument in sql datename function will display the Nanoseconds Value
TZOffsettzThis will display the Time Zone Offset Value.
ISO_WEEKisowk, isowwThis will display the Iso Week Number

Date: Please specify the valid date as the 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 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)
SQL DATENAME 1

DATENAME Function Example 2

In this example, we use one of the custom table to perform Datename operations on HireDate column.

The Datename function will print the Year value from the HireDate Column for the fifth line. 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]
SQL DATENAME 2