The DATENAME date function returns a character string representing the specified datepart of the specified date. The syntax of the DATENAME date function is as follows:
DATENAME ( <date part>, <date> )
The <date part> parameter specifies the part of the date to return. Valid values are YEAR or YY or YYYY, QUARTER or QQ or Q, MONTH or MM or M, DAYOFYEAR or DW or Y, DAY or DD or D, WEEK or WK or WW, WEEKDAY or DW, HOUR or HH, MINUTE or MI or N, SECOND or SS or S and MILLISECOND or MS. The <date> parameter is an expression that returns a DATETIME or SMALLDATETIME value, or a character string in a date format.
Here are a few uses of the DATENAME date function:
Usage #1 : Format Date into “Month DD, YYYY” Format
SELECT DATENAME(MM, GETDATE()) + ' ' + DATENAME(DD, GETDATE()) + ', ' + DATENAME(YY, GETDATE())
Usage #2 : Check for Holiday
SELECT CASE WHEN (MONTH(GETDATE()) = 7 AND DAY(GETDATE()) = 4 ) OR -- U.S. Independence Day
(MONTH(GETDATE()) = 1 AND DAY(GETDATE()) = 1 ) OR -- New Year's Day
(MONTH(GETDATE()) = 12 AND DAY(GETDATE()) = 25) OR -- Christmas Day
(MONTH(GETDATE()) = 11 AND DAY(GETDATE()) BETWEEN 22 AND 28
AND DATENAME(DW, GETDATE()) = 'Thursday') OR -- Thanksgiving
(MONTH(GETDATE()) = 5 AND DAY(GETDATE()) BETWEEN 25 AND 31
AND DATENAME(DW, GETDATE()) = 'Monday') OR -- Memorial Day
(MONTH(GETDATE()) = 9 AND DAY(GETDATE()) BETWEEN 1 AND 7
AND DATENAME(DW, GETDATE()) = 'Monday') -- Labor Day
THEN 'Holiday'
ELSE 'Not a Holiday' END AS [Is US Holiday]
Usage #3 : Generate a Report by Day of the Week Using the PIVOT Command
CREATE TABLE [dbo].[UserLog] (
[UserName] VARCHAR(20),
[LoginDate] DATETIME
)
GO
SELECT [UserName], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday]
FROM (SELECT [UserName], DATENAME(DW, [LoginDate]) AS [WeekDay]
FROM [dbo].[UserLog]) A
PIVOT
( COUNT([WeekDay])
FOR [WeekDay] IN (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday)) AS [Pvt]
ORDER BY [UserName]
Usage #4 : Generate a Sales Report by Month
SELECT DATENAME(YYYY, [TransactionDate]) + ' ' + DATENAME(MM, [TransactionDate]) AS [SalesMonth],
SUM([TransactionAmount]) AS [Total Sales], SUM([Quantity]) AS [Sales Volume]
FROM [dbo].[Sales]
GROUP BY DATENAME(YYYY, [TransactionDate]) + ' ' + DATENAME(MM, [TransactionDate])