Subtract year from date value in MariaDB

In MariaDB, to subtract a year from a date, you can use the SUBDATE() function or the subtraction operator.

Some examples are given below.

Example 1: SUBDATE() Function

The following statement uses the SUBDATE() function to subtract 1 year from a specified date:

SELECT SUBDATE('2022-12-13', INTERVAL 1 YEAR);

Output:

+----------------------------------------+
| SUBDATE('2022-12-13', INTERVAL 1 YEAR) |
+----------------------------------------+
| 2021-12-13                             |
+----------------------------------------+

The following statement uses the SUBDATE() function to subtract 1 year from the current date:

SELECT SUBDATE(CURDATE(), INTERVAL 1 YEAR);

Output:

+-------------------------------------+
| SUBDATE(CURDATE(), INTERVAL 1 YEAR) |
+-------------------------------------+
| 2021-12-13                          |
+-------------------------------------+

Here, INTERVAL 1 YEAR means 1 year. If you want to subtract years, say 10 years, you just need to change 1 to 10, like this:

SELECT SUBDATE(CURDATE(), INTERVAL 10 YEAR);

Output:

+--------------------------------------+
| SUBDATE(CURDATE(), INTERVAL 10 YEAR) |
+--------------------------------------+
| 2012-12-13                           |
+--------------------------------------+

Example 2: DATE_SUB() Function

DATE_SUB() and SUBDATE are used in exactly the same.

The following statement uses the DATE_SUB() function to subtract 1 year from a specified date:

SELECT DATE_SUB('2022-12-13', INTERVAL 1 YEAR);

Output:

+-----------------------------------------+
| DATE_SUB('2022-12-13', INTERVAL 1 YEAR) |
+-----------------------------------------+
| 2021-12-13                              |
+-----------------------------------------+

Example 3: ADDDATE() Function

The ADDDATE() function is opposite to the SUBDATE function, which is used to add the specified time period to the specified date. To subtract a period of time, just pass in a negative value.

The following statement uses the ADDDATE() function to subtract 1 year from a specified date:

SELECT ADDDATE('2022-12-13', INTERVAL -1 YEAR);

Output:

+-----------------------------------------+
| ADDDATE('2022-12-13', INTERVAL -1 YEAR) |
+-----------------------------------------+
| 2021-12-13                              |
+-----------------------------------------+

Example 4: Addition and subtraction operators

The following statement uses subtraction operator - to subtract 1 year from '2022-12-13':

SELECT '2022-12-13' - INTERVAL 1 YEAR;

Output:

+--------------------------------+
| '2022-12-13' - INTERVAL 1 YEAR |
+--------------------------------+
| 2021-12-13                     |
+--------------------------------+

Of course, you can also use the addition operator +. And just change the INTERVAL value to a negative number.

The following statement implements subtracting 1 year from '2022-12-13':

SELECT '2022-12-13' + INTERVAL -1 YEAR;

Output:

+---------------------------------+
| '2022-12-13' + INTERVAL -1 YEAR |
+---------------------------------+
| 2021-12-13                      |
+---------------------------------+

Conclusion

This article gives several methods to subtract a year from a specified date.