How to Extract Year from Date in SQL

Summary: in this tutorial, you’ll learn how to extract a year from a date value or column in SQL.

Extracting the year from a date using the EXTRACT function #

SQL offers the EXTRACT function to extract the year from a date value. Here’s the syntax of the EXTRACT function:

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

In this syntax, specify the date value (date_value) from which you want to extract the year after the FROM keyword.

If the date_value is not a DATE type, you may need to explicitly convert it to a value of the DATE type before passing it to the EXTRACT function.

To extract the year from a table column, you use the following syntax:

SELECT EXTRACT(YEAR FROM date_column)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

In this syntax, you pass the date_column to the EXTRACT function after the FROM keyword.

The following statement uses the EXTRACT function to extract the year from a date value:

SELECT EXTRACT(YEAR FROM '2025-01-19'::DATE) the_year;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 the_year
----------
     2025Code language: SQL (Structured Query Language) (sql)

In this example, we use the cast operator (::) to cast a string to a value of the DATE type in PostgreSQL.

The following example uses the EXTRACT function to extract the year from the hire_date column of the employees table:

SELECT
  hire_date,
  EXTRACT(YEAR FROM  hire_date) joined_year
FROM
  employees
ORDER BY
  joined_year DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 hire_date  | joined_year
------------+-------------
 2000-01-04 |        2000
 1999-12-07 |        1999
 1999-05-24 |        1999
 1999-02-07 |        1999
...Code language: SQL (Structured Query Language) (sql)

Extracting the year from a date using the YEAR function #

Many database systems, including PostgreSQL, Oracle, and Firebird, widely support the EXTRACT function.

However, database systems like MySQL, MariaDB, SQL Server, Db2, SAP HANA, Informix, and Teradata do not support the EXTRACT function. Instead, they offer the YEAR function.

Here’s the syntax of the YEAR function:

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

You can also use a date column for the YEAR function:

SELECT
  YEAR (date_column)
FROM
  table_name;Code language: SQL (Structured Query Language) (sql)

For example, you can extract the year from a date in MySQL using the YEAR function as follows:

SELECT
  first_name,
  hire_date,
  YEAR (hire_date) joined_year
FROM
  employees
ORDER BY
  joined_year;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  | hire_date  | joined_year
-------------+------------+-------------
 Jennifer    | 1987-09-17 |        1987
 Steven      | 1987-06-17 |        1987
 Neena       | 1989-09-21 |        1989
 Alexander   | 1990-01-03 |        1990
...Code language: SQL (Structured Query Language) (sql)

Extracting the year from a date in SQLite #

SQLite does not support both EXTRACT and YEAR functions. Instead, it offers the strftime() function that you can format a date to a year using the '%Y' format string:

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

For example, you can extract the year from a date using the strftime function as follows:

SELECT strftime('%Y', '2025-01-19') the_year;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 the_year
----------
     2025Code language: SQL (Structured Query Language) (sql)

The following table shows the databases and their corresponding function to extract the year from a date: