SQL Server YEAR Function

Summary: in this tutorial, you will learn how to use the SQL Server YEAR() function to extract a year from a date.

SQL Server YEAR() function overview

The YEAR() function returns an integer value which represents the year of the specified date.

The following shows the syntax of the YEAR() function:

YEAR(input_date)
Code language: SQL (Structured Query Language) (sql)

The function accepts an argument which can be a literal date value or an expression that can resolve to a TIME, DATE, SMALLDATETIME, DATETIME, DATETIME2, or DATETIMEOFFSET value.

The YEAR() function returns the same value as the following DATEPART() function:

DATEPART(year,input_date)
Code language: SQL (Structured Query Language) (sql)

SQL Server YEAR() function examples

A) Using YEAR() function with a literal date value

This example uses the YEAR() function to extract a year from the date ‘2019-02-01’:

SELECT 
	YEAR('2019-02-01') [year];
Code language: SQL (Structured Query Language) (sql)

The output is:

year
-----------
2019

(1 row affected)

B) Using YEAR() function with a date value that has only time part

If the input date value has only time data, the YEAR() function will return 1900:

SELECT 
	YEAR('11:05:01') [year];
Code language: SQL (Structured Query Language) (sql)

Here is the output:

year
-----------
1900

(1 row affected)

C) Using YEAR() function with table columns example

We will use the sales.orders and sales.order_items from the sample database for demonstration.

This example uses the YEAR() function to extract year data from the values in the shipped_date column. It returns the gross sales by year using the SUM() function and GROUP BY clause:

SELECT YEAR(shipped_date) [year], 
	   SUM(list_price * quantity) gross_sales
FROM sales.orders o
	 INNER JOIN sales.order_items i ON i.order_id = o.order_id
WHERE shipped_date IS NOT NULL
GROUP BY YEAR(shipped_date)
order by [year];
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

year        gross_sales
----------- -----------
2016        2649649.97
2017        3725890.76
2018        1062469.33

(3 rows affected)Code language: CSS (css)

In this tutorial, you have learned how to extract the year from a specified date by using the SQL Server YEAR() function.