How to Extract a Year from a Date in SQL

Summary: in this tutorial, you will learn how to extract the year from a date in SQL by using various functions.

Given a DATE or DATETIME value, you can extract the year from it by using the EXTRACT() function. The following illustrates the syntax:

EXTRACT(YEAR FROM date)
Code language: SQL (Structured Query Language) (sql)

The syntax is straightforward. The date can be a date literal or an expression that evaluates to a date value.

The EXTRACT() function returns a number which represents the year of the date.

The following example shows how to extract the year from the date of July 22nd 2018:

SELECT EXTRACT(YEAR FROM '2018-07-22')
Code language: SQL (Structured Query Language) (sql)

The result is 2018 as we expected:

2018
Code language: SQL (Structured Query Language) (sql)

To get the current year, you pass the current date to the EXTRACT() function as follows:

SELECT EXTRACT(YEAR FROM CURRENT_DATE)
Code language: SQL (Structured Query Language) (sql)

The EXTRACT() function is a SQL standard function supported by MySQL, Oracle, PostgreSQL, and Firebird.

If you use SQL Server, you can use the YEAR() or DATEPART() function to extract the year from a date.

For example, the following statement returns the current year in SQL Server:

SELECT YEAR(CURRENT_TIMESTAMP);

SELECT DATEPART(year, CURRENT_TIMESTAMP);
Code language: SQL (Structured Query Language) (sql)

Similar to SQL Server, MySQL also supports the YEAR() function to return the year from a date.

In SQLite, you use the strftime() function to get the current year from a date as shown in the following query:

SELECT strftime('%Y', CURRENT_TIMESTAMP)
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use various functions to extract the year from a date.

Was this tutorial helpful ?