Tip of the Day : Similarities and Differences Between LEN and DATALENGTH Functions

SQL Server Helper - Tip of the Day

Example Uses of the YEAR Date 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.

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]

Back to Tip of the Day List Next Tip