SQL Server Helper - Tip of the Day

The YEAR date function returns an integer that represents the year of the specified date parameter.  The syntax of the YEAR date function is as follows:

YEAR ( <date> )

The <date> parameter is an expression that can be resolved to a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2 or DATETIMEOFFSET value and can be an expression, column expression, user-defined variable or string literal.

Here are a few uses of the YEAR date function:

Usage #1 : Age Calculation/Computation

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 : Determine if it’s a Leap Year

SELECT CASE WHEN (YEAR(GETDATE()) % 4 = 0 AND YEAR(GETDATE()) % 100 != 0) OR
                  YEAR(GETDATE()) % 400 = 0
            THEN 'Leap Year'
            ELSE 'Not a Leap Year' END AS [Is Leap Year]

Usage #3 : 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 #4 : Get Number of Days in a Year

SELECT 365 + CASE WHEN (YEAR(GETDATE()) % 4 = 0 AND YEAR(GETDATE()) % 100 != 0) OR
                        YEAR(GETDATE()) % 400 = 0
                  THEN 1
                  ELSE 0 END AS [Number of Days in a Year]

Usage #5 : Format Date in YYYYMMDD Date Format

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

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

DECLARE @NewNumber        DECIMAL(10, 0)

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

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

Usage #7 : Get Year in YY Format

SELECT YEAR(GETDATE()) % 100 AS [YY]