YEAR Date and Time Function¶

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.

Sample Uses of the YEAR Date Function¶

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]