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 takes a date argument and returns the year of the date. See the syntax of the YEAR() function:
YEAR(date);
Code language: SQL (Structured Query Language) (sql)
The YEAR() function returns a year value in the range 1000 to 9999. If the date is zero, the YEAR() function returns 0.
The following example returns the year of January 1st 2017 which is 2017.
SELECT YEAR('2017-01-01');| YEAR(‘2017-01-01’) |
+——————–+
| 2017 |
+——————–+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
The following statement returns the current year:
SELECT YEAR(NOW());
+-------------+
| YEAR(NOW()) |
+-------------+
| 2017 |
+-------------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
In this example, the YEAR() function returns the year information of the current date and time provided by the NOW() function.
If the date is NULL, the YEAR() function will return NULL as shown in the following example:
SELECT YEAR(NULL);
+------------+
| YEAR(NULL) |
+------------+
| NULL |
+------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
As mentioned earlier, the YEAR() of zero date is zero:
SELECT YEAR('0000-00-00');
+--------------------+
| YEAR('0000-00-00') |
+--------------------+
| 0 |
+--------------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)
Let’s see the orders table in the sample database.
The following query uses the YEAR() function to get the number of orders shipped per year.
SELECT
YEAR(shippeddate) year,
COUNT(ordernumber) orderQty
FROM
orders
WHERE
shippeddate IS NOT NULL
GROUP BY YEAR(shippeddate)
ORDER BY YEAR(shippeddate);Code language: SQL (Structured Query Language) (sql)
In this example, we use the YEAR() function to extract year information out of the shipped date and use the COUNT() function to count the number of delivered orders. The GROUP BY clause group the number of orders by year.
MySQL YEAR function and indexes
Currently, MySQL does no support function index. It means that the expression YEAR(column) will not leverage index if the index is available for the column.
We will create a new table named dates for demonstration purpose:
CREATE TABLE dates (
id INT PRIMARY KEY AUTO_INCREMENT,
dt DATE
);
Code language: SQL (Structured Query Language) (sql)
The dt column will store the date data. The following statement creates an index on the dt column of the dates table.
CREATE INDEX idx_td ON dates(dt);
Code language: SQL (Structured Query Language) (sql)
We will insert lots of dates into the dates table. The easiest way is to use a recursive CTE to generate the date series and insert this date series into the dates table.
The following recursive CTE generates the dates between '1800-01-01' and '2020-12-31':
WITH RECURSIVE dates (dt) AS
(
SELECT '1800-01-01'
UNION ALL
SELECT dt + INTERVAL 1 DAY FROM dates
WHERE dt + INTERVAL 1 DAY <= '2020-12-31'
)
SELECT dt FROM dates;
Code language: SQL (Structured Query Language) (sql)
To insert this date series into the dates table, you use the following INSERT statement:
INSERT INTO dates(dt)
WITH RECURSIVE dates (dt) AS
(
SELECT '1800-01-01'
UNION ALL
SELECT dt + INTERVAL 1 DAY FROM dates
WHERE dt + INTERVAL 1 DAY <= '2020-01-01'
)
SELECT dt FROM dates;
Code language: SQL (Structured Query Language) (sql)
You can find the number of rows in the dates table by using the following query:
SELECT
COUNT(*)
FROM
dates;
Code language: SQL (Structured Query Language) (sql)
The dates table has 80354 rows.
To get all the dates in 2014, you use the following query:
SELECT
*
FROM
dates
WHERE
YEAR(dt) = 2014;
Code language: SQL (Structured Query Language) (sql)
Or
SELECT
*
FROM
dates
WHERE
dt BETWEEN '2014-01-01' and '2014-12-31';
Code language: SQL (Structured Query Language) (sql)
Both queries return 365 rows, which is correct.
However, there is a difference in terms of performance. The first query examines all rows in the dates table and some rows in the index while the second one uses the index only which is much faster.
See the EXPLAIN of both queries:
EXPLAIN SELECT
*
FROM
dates
WHERE
YEAR(dt) = 2014;
Code language: SQL (Structured Query Language) (sql)
And
EXPLAIN SELECT
*
FROM
dates
WHERE
dt BETWEEN '2014-01-01' and '2014-12-31';
Code language: SQL (Structured Query Language) (sql)
Even though the MySQL YEAR() function is handy, when it comes to performance, you should always consider using it.
In this tutorial, we have introduced you the MySQL YEAR() function and gave you some examples of using it.