In MariaDB, YEAR() is a built-in date and time function that returns the year from a given date expression.
It accepts one argument, which is the date you want to extract the year from.
It returns the year as a number in the range 1000 to 9999. For zero dates (e.g. 0000-00-00), the result is 0.
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('2030-08-01');Result:
+--------------------+
| YEAR('2030-08-01') |
+--------------------+
| 2030 |
+--------------------+
Datetime Values
It also works with datetime values:
SELECT YEAR('2030-08-01 10:30:45');Result:
+-----------------------------+
| YEAR('2030-08-01 10:30:45') |
+-----------------------------+
| 2030 |
+-----------------------------+
Zero Dates
Zero dates result in 0.
Example:
SELECT YEAR('0000-00-00');Result:
+--------------------+
| YEAR('0000-00-00') |
+--------------------+
| 0 |
+--------------------+
Numeric Dates
It’s also possible to pass dates as a number, as long as it makes sense as a date.
Example
SELECT YEAR(20301125);Result:
+----------------+ | YEAR(20301125) | +----------------+ | 2030 | +----------------+
Or even the following (which uses a two-digit year):
SELECT YEAR(301125);Result:
+--------------+ | YEAR(301125) | +--------------+ | 2030 | +--------------+
But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:
SELECT YEAR(20301135);Result:
+----------------+ | YEAR(20301135) | +----------------+ | NULL | +----------------+ 1 row in set, 1 warning (0.000 sec)
We can check the warning like this:
SHOW WARNINGS;Result:
+---------+------+--------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------+ | Warning | 1292 | Incorrect datetime value: '20301135' | +---------+------+--------------------------------------+
Other Delimiters
You can use other delimiters for the date. MariaDB is quite forgiving when it comes to delimiters on dates. Here are some valid examples:
SELECT
YEAR('2030/06/25'),
YEAR('2030,06,25'),
YEAR('2030:06:25'),
YEAR('2030;06!25');Result (using vertical output):
YEAR('2030/06/25'): 2030
YEAR('2030,06,25'): 2030
YEAR('2030:06:25'): 2030
YEAR('2030;06!25'): 2030
Current Date
We can pass NOW() as the datetime argument to use the current date:
SELECT
NOW(),
YEAR(NOW());Result:
+---------------------+-------------+ | NOW() | YEAR(NOW()) | +---------------------+-------------+ | 2021-05-18 08:55:02 | 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.001 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 MariaDB 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 MariaDB server version for the right syntax to use near ' '2031-12-10')' at line 1