SQL Server Helper - Tip of the Day

The MONTH date function returns an integer that represents the month part of a specified date.  The syntax of the MONTH date function is as follows:

MONTH ( <date> )

The <date> parameter is an expression that returns a DATETIME or SMALLDATETIME value, or a character string in a date format.  The MONTH date function is equivalent to DATEPART(MM, date).

Here are a few uses of the MONTH date function:

Usage #1 : 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 #2 : Get Number of Days in a Month

SELECT CASE WHEN MONTH(GETDATE()) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
            WHEN MONTH(GETDATE()) IN (4, 6, 9, 1) THEN 30
            WHEN (YEAR(GETDATE()) % 4 = 0 AND YEAR(GETDATE()) % 100 != 0) OR
                  YEAR(GETDATE()) % 400 = 0
            THEN 29
            ELSE 28 END AS [Number of Days in a Month]

Usage #3 : 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 #4 : Format Date in YYYYMMDD Date Format

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

Usage #5 : Generate Account Number, Reference Number or Tracking Number

DECLARE @NewNumber        DECIMAL(10, 0)

SELECT @NewNumber = MAX([TrackingNumber]) + 1
FROM [dbo].[Tracking]
WHERE [TrackingNumber] > YEAR(GETDATE()) * 1000000 + MONTH(GETDATE()) * 10000

IF @NewNumber IS NULL
    SET @NewNumber = YEAR(GETDATE()) * 1000000 + MONTH(GETDATE()) * 10000 + 1