SQL Server Helper - Tip of the Day

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]