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]