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]