In MySQL, YEAR() is a built-in date and time function that returns the year from a given date expression.

It returns the year as a number in the range 1000 to 9999. For zero dates, it could return 0 or NULL with a warning, depending on the values in your sql_mode.

Syntax

The syntax goes like this:

YEAR(date)

Where date is the date expression to get the year from.

Example

Here’s an example:

SELECT YEAR('2045-12-10');

Result:

2045

Datetime Values

It also works with datetime values:

SELECT YEAR('2045-12-10 18:52:17');

Result:

2045

Zero Dates

According to the MySQL documentation, zero dates result in 0. However, the actual result will depend on the value of your sql_mode system variable.

By default, zero dates are not allowed, and therefore, doing the following produces NULL with a warning:

SELECT YEAR('0000-00-00');

Result:

+--------------------+
| YEAR('0000-00-00') |
+--------------------+
|               NULL |
+--------------------+
1 row in set, 1 warning (0.00 sec)

And here’s the warning:

show warnings;

Result:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '0000-00-00' |
+---------+------+----------------------------------------+

I got this warning because my sql_mode system variable includes NO_ZERO_DATE and NO_ZERO_IN_DATE:

SELECT @@SESSION.sql_mode;

Result:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Let’s reset my sql_mode without those values:

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
SELECT @@SESSION.sql_mode;

Result:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

Now let’s run the YEAR() function again with the zero date:

SELECT YEAR('0000-00-00');

Result:

0

This time we get 0, as per the documentation.

Note that NO_ZERO_DATE and NO_ZERO_IN_DATE are deprecated at the time of writing. Therefore, they could be removed at any time.

Numeric Dates

It’s also possible to pass dates as a number, as long as it makes sense as a date.

Example:

SELECT YEAR(20451210);

Result:

2045

Or even the following (which uses a two-digit year):

SELECT YEAR(451210);

Result:

2045

But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:

SELECT YEAR(20451265);

Result:

+----------------+
| YEAR(20451265) |
+----------------+
|           NULL |
+----------------+
1 row in set, 1 warning (0.00 sec)

We can check the warning like this:

show warnings;

Result:

+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '20451265' |
+---------+------+--------------------------------------+

Other Delimiters

You can use other delimiters for the date. MySQL is quite forgiving when it comes to delimiters on dates. Here are some valid examples:

SELECT 
    YEAR('2045/12/10'),
    YEAR('2045,12,10'),
    YEAR('2045:12:10'),
    YEAR('2045;12!10');

Result (using vertical output):

YEAR('2045/12/10'): 2045
YEAR('2045,12,10'): 2045
YEAR('2045:12:10'): 2045
YEAR('2045;12!10'): 2045

Current Date

We can pass NOW() as the datetime argument to use the current date:

SELECT 
    NOW(),
    YEAR(NOW());

Result:

+---------------------+-------------+
| NOW()               | YEAR(NOW()) |
+---------------------+-------------+
| 2021-10-17 11:09:23 |        2021 |
+---------------------+-------------+

Invalid Arguments

When passed an invalid argument, YEAR() returns null:

SELECT YEAR('2030-65-78');

Result:

+--------------------+
| YEAR('2030-65-78') |
+--------------------+
|               NULL |
+--------------------+
1 row in set, 1 warning (0.00 sec)

Check the warning:

SHOW WARNINGS;

Result:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2030-65-78' |
+---------+------+----------------------------------------+

Missing Argument

Calling YEAR() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT YEAR();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

And another example:

SELECT YEAR('2030-12-10', '2031-12-10');

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', '2031-12-10')' at line 1