Tip of the Day : Similarities and Differences Between LEN and DATALENGTH Functions

SQL Server Helper - Tip of the Day

Example Uses of the DAY Date Function

The DAY date function returns an integer representing the day DATEPART of the specified date.  This function is equivalent to DATEPART(DD, <date>).  The syntax of the DAY date function is as follows:

DAY ( <date> )

The <date> parameter is an expression of type DATETIME or SMALLDATETIME.

Here are a few uses of the DAY date function:

Usage #1 : Get Number of Days in a Month

SELECT DAY(DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0) - 1) AS [Number of Days in the Month]

Usage #2: Determine if Today is the Last Day of the Month

SELECT CASE WHEN DAY(GETDATE()) > DAY(GETDATE() + 1)
            THEN 'Today is the last day of the month'
            ELSE 'Today is NOT the last day of the month' END AS [Last Day]

Usage #3 : Get the Last Day of the Previous Month

SELECT DATEADD(DD, -DAY(GETDATE()), GETDATE()) AS [Last Day of Previous Month With Time]
SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()),  -DAY(GETDATE())) AS [Last Day of Previous Month]

Usage #4 : 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 #5 : Calculate the Average Daily Balance

SELECT [AggregateBalance] / DAY(GETDATE()) AS [Average Daily Balance]
FROM [dbo].[AccountBalance]

Usage #6 : Age Computation / Calculation

DECLARE @BirthDate    DATETIME = '1776/07/04'

SELECT YEAR(GETDATE()) - YEAR(@BirthDate) -
       CASE WHEN MONTH(GETDATE()) > MONTH(@BirthDate) OR
                (MONTH(GETDATE()) = MONTH(@BirthDate) AND
                 DAY(GETDATE())  >= DAY(@BirthDate))
            THEN 0 ELSE 1 END AS [Age of America]

Usage #7 : Format Date in YYYYMMDD Date Format

SELECT YEAR(GETDATE()) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()) AS [YYYYMMDD]

Back to Tip of the Day List Next Tip