MariaDB provides several ways to add a year or a certain number of years to a given date. For example, you might want to add 1 year, 10 years, 30 years, etc.

Here are 6 ways to add a year to a date in MariaDB.

The DATE_ADD() Function

The DATE_ADD() function allows you to add an amount to a date. For example you can add a number of days, weeks, months, years etc.

Example:

SELECT DATE_ADD('2021-05-01', INTERVAL 1 YEAR);

Result:

+-----------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 1 YEAR) |
+-----------------------------------------+
| 2022-05-01                              |
+-----------------------------------------+

There’s no rule that says it can only be 1 year. You can add multiple years. Either way, the YEAR keyword remains non-plural:

SELECT DATE_ADD('2021-05-01', INTERVAL 10 YEAR);

Result:

+------------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 10 YEAR) |
+------------------------------------------+
| 2031-05-01                               |
+------------------------------------------+

It’s also possible to use another date/time unit with the appropriate number of that unit. However, this approach should be used with caution, as it could result in unexpected results.

Example:

SELECT 
    DATE_ADD('2021-05-01', INTERVAL 365 DAY) AS Days,
    DATE_ADD('2021-05-01', INTERVAL 52 WEEK) AS Weeks,
    DATE_ADD('2021-05-01', INTERVAL 12 MONTH) AS Months,
    DATE_ADD('2021-05-01', INTERVAL 1 YEAR) AS Years;

Result:

+------------+------------+------------+------------+
| Days       | Weeks      | Months     | Years      |
+------------+------------+------------+------------+
| 2022-05-01 | 2022-04-30 | 2022-05-01 | 2022-05-01 |
+------------+------------+------------+------------+

In this case, adding 52 weeks had a different result to using 365 days, 12 months, and 1 year.

If we move the date forward a couple of years, we get even more inconsistency:

SELECT 
    DATE_ADD('2023-05-01', INTERVAL 365 DAY) AS Days,
    DATE_ADD('2023-05-01', INTERVAL 52 WEEK) AS Weeks,
    DATE_ADD('2023-05-01', INTERVAL 12 MONTH) AS Months,
    DATE_ADD('2023-05-01', INTERVAL 1 YEAR) AS Years;

Result:

+------------+------------+------------+------------+
| Days       | Weeks      | Months     | Years      |
+------------+------------+------------+------------+
| 2024-04-30 | 2024-04-29 | 2024-05-01 | 2024-05-01 |
+------------+------------+------------+------------+

This time using MONTH and YEAR returns the same result, but DAY and WEEK returns two different dates, both of which are different to the MONTH and YEAR results.

The ADDDATE() Function

Another way to add a year to a date is to use the ADDDATE() function. This function has two syntaxes. One syntax allows you to add a number of days to a date. The other syntax is the same as with the DATE_ADD() function above. When using this syntax, ADDDATE() is a synonym for DATE_ADD().

Example:

SELECT ADDDATE('2021-05-01', INTERVAL 1 YEAR);

Result:

+----------------------------------------+
| ADDDATE('2021-05-01', INTERVAL 1 YEAR) |
+----------------------------------------+
| 2022-05-01                             |
+----------------------------------------+

The DATE_SUB() Function

The DATE_SUB() function allows you to subtract an amount to a date. However, if you provide a negative value to subtract, then it ends up being added to the date.

Example:

SELECT DATE_SUB('2021-05-01', INTERVAL -1 YEAR);

Result:

+------------------------------------------+
| DATE_SUB('2021-05-01', INTERVAL -1 YEAR) |
+------------------------------------------+
| 2022-05-01                               |
+------------------------------------------+

The SUBDATE() Function

The SUBDATE() function is a synonym for DATE_SUB() when using the following syntax.

Example:

SELECT SUBDATE('2021-05-01', INTERVAL -1 YEAR);

Result:

+-----------------------------------------+
| SUBDATE('2021-05-01', INTERVAL -1 YEAR) |
+-----------------------------------------+
| 2022-05-01                              |
+-----------------------------------------+

The + Operator

Another option is to use the + operator.

The + operator is used to perform addition, and we can use it on dates, along with the applicable date/time unit to add a certain number of years to our date.

Example:

SELECT '2021-05-01' + INTERVAL 1 YEAR;

Result:

+--------------------------------+
| '2021-05-01' + INTERVAL 1 YEAR |
+--------------------------------+
| 2022-05-01                     |
+--------------------------------+

The - Operator

The - operator can be used to subtract an amount from a date. But if it’s used to subtract a negative number, then the result is a positive amount being added to the date:

SELECT '2021-05-01' - INTERVAL -1 YEAR;

Result:

+---------------------------------+
| '2021-05-01' - INTERVAL -1 YEAR |
+---------------------------------+
| 2022-05-01                      |
+---------------------------------+

Other Date and Time Units

The above examples add a number of years to the date, but we can use the same techniques to add any date/time unit. See MariaDB Date and Time Units for a full list of date/time units that can be used with the above functions and operator.