3 Ways to Extract the Year from a Date in SQL Server (T-SQL)

There are several ways to return the year from a date in SQL Server. Here are three (or is it four?).

`YEAR()`

The most obvious method is to use the YEAR() function. This function returns an integer with the year portion of the specified date.

DECLARE @date date = '2020-10-25';
SELECT YEAR(@date);

Result:

Another way to do it is to use the DATEPART() function. As with the YEAR() function, DATEPART() also returns the result as an integer.

DECLARE @date date = '2020-10-25';
SELECT DATEPART(year, @date);

Result:

Alternatively, the first argument can be yy or yyyy to produce the same result.

The FORMAT() function is a bit different to the previous two, because it returns its result as a string (nvarchar or null).

DECLARE @date date = '2020-10-25'
SELECT FORMAT(@date, 'yyyy');

Result:

The FORMAT() function also enables you to provide the short year (i.e. yy).

Example:

DECLARE @date date = '1979-10-25'
SELECT 
  FORMAT(@date, 'yyyy') AS [yyyy],
  FORMAT(@date, 'yy') AS [yy];

Result:

+--------+------+
 | yyyy   | yy   |
 |--------+------|
 | 1979   | 79   |
 +--------+------+