MySQL YEAR() Function

Summary: in this tutorial, you will learn how to use the MySQL YEAR() function to get the year out of a date value.

Introduction to MySQL YEAR() function

The YEAR() function returns a year from a date value. Here’s the basic syntax of the YEAR() function:

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

In this syntax:

date: This is the date or datetime value from which you want to get the year.

The YEAR() function returns an integer that represents the year part of the provided date. It has a range of 1000 and 9999.

If the date is zero, the YEAR() function returns 0. If the date is NULL, the YEAR() function returns NULL.

MySQL YEAR() function examples

Let’s take some examples of using the YEAR() function.

1) Simple YEAR() function examples

The following example uses the YEAR() function to extract the year of January 1st 2017:

SELECT YEAR('2017-01-01');Code language: JavaScript (javascript)

Output:

+--------------------+
| YEAR('2017-01-01') |
+--------------------+
|               2017 |
+--------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

To get the current year, you use the NOW() function to get the current date and time and pass it to the YEAR() function as follows:

SELECT YEAR(NOW());

If the date is NULL, the YEAR() function will return NULL as shown in the following example:

SELECT YEAR(NULL);Code language: PHP (php)

Output:

+------------+
| YEAR(NULL) |
+------------+
|       NULL |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

As mentioned earlier, the YEAR() of a zero date is zero:

SELECT YEAR('0000-00-00');Code language: JavaScript (javascript)

Output:

+--------------------+
| YEAR('0000-00-00') |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using the YEAR() function with table data

We’ll use the orders table from the sample database:

The following query uses the YEAR() function to retrieve the number of orders shipped per year

SELECT 
  YEAR(shippeddate) year, 
  COUNT(ordernumber) shippedOrderQty 
FROM 
  orders 
WHERE 
  shippeddate IS NOT NULL 
GROUP BY 
  YEAR(shippeddate) 
ORDER BY 
  YEAR(shippeddate);Code language: SQL (Structured Query Language) (sql)

Output:

+------+-----------------+
| year | shippedOrderQty |
+------+-----------------+
| 2003 |             110 |
| 2004 |             147 |
| 2005 |              55 |
+------+-----------------+
3 rows in set (0.00 sCode language: JavaScript (javascript)

The query does the following:

First, select data from the orders table.
Second, filter out orders that haven’t been shipped.
Third, group the remaining rows by the year in which they were shipped and count the number of orders for each year.
Finally, sort the result set by year in ascending order

It provides useful insights into the trend of order shipments over the years.

MySQL YEAR function and indexes

When you use the YEAR() function in a query, MySQL may not use an index. This can lead to a decrease in the speed of data retrieval. It’ll be fine if the table has a few rows.

But it’ll be an issue when the number of rows in the table grows. To fix this, you need to use a functional index on the date column for using the YEAR() function. Consider the following example.

First, create a new table named tests for demonstration purposes:

CREATE TABLE tests(
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL
); Code language: SQL (Structured Query Language) (sql)

The date column will store the date data.

Second, insert many rows into the tests table using a recursive CTE that generates a series of dates:

INSERT INTO tests(date)
WITH RECURSIVE data(date) AS
(
  SELECT '2019-06-03'
  UNION ALL
  SELECT date + INTERVAL 1 DAY FROM data
  WHERE date + INTERVAL 1 DAY <= '2020-31-12'
)
SELECT date FROM data;Code language: SQL (Structured Query Language) (sql)

Third, get the number of rows in the tests table:

SELECT 
    COUNT(*)
FROM
    tests; Code language: SQL (Structured Query Language) (sql)

Output:

+----------+
| COUNT(*) |
+----------+
|      578 |
+----------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)

The tests table has 578 rows.

Finally, retrieve all the rows whose dates are in 2019:

SELECT 
  * 
FROM 
  tests 
WHERE 
  YEAR(date) = 2019;Code language: SQL (Structured Query Language) (sql)

It returned 212 rows.

The following statement explains how MySQL retrieves data:

EXPLAIN SELECT * 
FROM tests WHERE YEAR(date) = 2019;

Output:

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tests | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  578 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)Code language: PHP (php)

The query does not use an index.

To improve the performance, you can create a functional index on the date column of the tests table á follows:

ALTER TABLE tests
ADD INDEX YEAR(date);

The following query shows that querying data from the tests table based on the YEAR() function of the date column will use the index:

EXPLAIN SELECT * 
FROM tests WHERE YEAR(date) = 2019;

Output:

+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | tests | NULL       | index | NULL          | YEAR | 3       | NULL |  578 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)Code language: PHP (php)

Summary

Use the YEAR() function to extract the year of a date value.

Was this tutorial helpful?