{"id":1581,"date":"2018-07-21T18:03:54","date_gmt":"2018-07-22T01:03:54","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=1581"},"modified":"2025-01-20T20:39:26","modified_gmt":"2025-01-21T03:39:26","slug":"how-to-extract-year-from-date-in-sql","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-date-functions\/how-to-extract-year-from-date-in-sql\/","title":{"rendered":"How to Extract Year from Date in SQL"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to extract a year from a date value or column in SQL.<\/p>\n\n\n\n

Extracting the year from a date using the EXTRACT function #<\/a><\/h2>\n\n\n\n

SQL offers the EXTRACT<\/a><\/code> function to extract the year from a date value. Here’s the syntax of the EXTRACT<\/code> function:<\/p>\n\n\n

EXTRACT(YEAR FROM date_value)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, specify the date value (date_value<\/code>) from which you want to extract the year after the FROM<\/code> keyword.<\/p>\n\n\n\n
If the date_value<\/code> is not a DATE<\/code> type, you may need to explicitly convert it to a value of the DATE<\/code> type before passing it to the EXTRACT<\/code> function.<\/p>\n\n\n\n
To extract the year from a table column, you use the following syntax:<\/p>\n\n\n
SELECT<\/span> EXTRACT<\/span>(YEAR<\/span> FROM<\/span> date_column)\nFROM<\/span> table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, you pass the date_column<\/code> to the EXTRACT<\/code> function after the FROM<\/code> keyword.<\/p>\n\n\n\n
The following statement uses the EXTRACT<\/code> function to extract the year from a date value:<\/p>\n\n\n
SELECT<\/span> EXTRACT<\/span>(YEAR<\/span> FROM<\/span> '2025-01-19'<\/span>::DATE<\/span>) the_year;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 the_year\n----------<\/span>\n     2025<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, we use the cast operator (::<\/code>) to cast a string to a value of the DATE<\/code> type in PostgreSQL.<\/p>\n\n\n\n
The following example uses the EXTRACT<\/code> function to extract the year from the hire_date<\/code> column of the employees<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  hire_date,\n  EXTRACT<\/span>(YEAR<\/span> FROM<\/span>  hire_date) joined_year\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  joined_year DESC<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 hire_date  | joined_year\n------------+-------------<\/span>\n 2000-01-04 |        2000\n 1999-12-07 |        1999\n 1999-05-24 |        1999\n 1999-02-07 |        1999\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Extracting the year from a date using the YEAR function #<\/a><\/h2>\n\n\n\n
Many database systems, including PostgreSQL, Oracle, and Firebird, widely support the EXTRACT<\/code> function.<\/p>\n\n\n\n
However, database systems like MySQL, MariaDB, SQL Server, Db2, SAP HANA, Informix, and Teradata do not support the EXTRACT<\/code> function. Instead, they offer the YEAR<\/code> function.<\/p>\n\n\n\n
Here’s the syntax of the YEAR<\/code> function:<\/p>\n\n\n
YEAR(date_value)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
You can also use a date column for the YEAR<\/code> function:<\/p>\n\n\n
SELECT<\/span>\n  YEAR<\/span> (date_column)\nFROM<\/span>\n  table_name;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, you can extract the year from a date in MySQL using the YEAR<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span>\n  first_name,\n  hire_date,\n  YEAR<\/span> (hire_date) joined_year\nFROM<\/span>\n  employees\nORDER<\/span> BY<\/span>\n  joined_year;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 first_name  | hire_date  | joined_year\n-------------+------------+-------------<\/span>\n Jennifer    | 1987-09-17 |        1987\n Steven      | 1987-06-17 |        1987\n Neena       | 1989-09-21 |        1989\n Alexander   | 1990-01-03 |        1990\n...<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Extracting the year from a date in SQLite #<\/a><\/h2>\n\n\n\n
SQLite does not support both EXTRACT<\/code> and YEAR<\/code> functions. Instead, it offers the strftime()<\/code> function that you can format a date to a year using the '%Y'<\/code> format string:<\/p>\n\n\n
strftime('%Y', date_column)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, you can extract the year from a date using the strftime<\/code> function as follows:<\/p>\n\n\n
SELECT<\/span> strftime('%Y'<\/span>, '2025-01-19'<\/span>) the_year;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 the_year\n----------<\/span>\n     2025<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following table shows the databases and their corresponding function to extract the year from a date:<\/p>\n\n\n\n
RDBMS<\/th>Function<\/th>Example<\/th><\/tr><\/thead>
PostgreSQL<\/strong><\/td>EXTRACT(YEAR FROM date)<\/code><\/td>SELECT EXTRACT(YEAR FROM '2025-01-19'::DATE);<\/code><\/td><\/tr>
MySQL<\/strong><\/td>YEAR(date)<\/code><\/td>SELECT YEAR('2025-01-19');<\/code><\/td><\/tr>
MariaDB<\/strong><\/td>YEAR(date<\/code>)<\/code><\/td>SELECT YEAR('2025-01-19');<\/code><\/td><\/tr>
Oracle<\/strong><\/td>EXTRACT(YEAR FROM date)<\/code> <\/td>SELECT EXTRACT(YEAR FROM DATE '2025-01-19') FROM DUAL;<\/code><\/td><\/tr>
SQL Server<\/strong><\/td>YEAR(date)<\/code><\/td>SELECT YEAR('2025-01-19');<\/code><\/td><\/tr>
SQLite<\/strong><\/td>strftime('%Y', date)<\/code><\/td>SELECT strftime('%Y', '2025-01-19');<\/code><\/td><\/tr>
DB2<\/strong><\/td>YEAR(date)<\/code><\/td>SELECT YEAR(DATE('2025-01-19')) FROM SYSIBM.SYSDUMMY1;<\/code><\/td><\/tr>
Firebird<\/strong><\/td>EXTRACT(YEAR FROM date)<\/code><\/td>SELECT EXTRACT(YEAR FROM DATE '2025-01-19') FROM RDB$DATABASE;<\/code><\/td><\/tr>
SAP HANA<\/strong><\/td>YEAR(date)<\/code><\/td>SELECT YEAR('2025-01-19') FROM DUMMY;<\/code><\/td><\/tr>
Teradata<\/strong><\/td>EXTRACT(YEAR FROM date)<\/code><\/td>SELECT EXTRACT(YEAR FROM DATE '2025-01-19');<\/code><\/td><\/tr>
Informix<\/strong><\/td>YEAR(date)<\/code><\/td>SELECT YEAR('2025-01-19') FROM systables WHERE tabid = 1;<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n