SQL Server Helper - Tip of the Day

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])