How to Subtract a Year from a Date in MariaDB

by

MariaDB provides several ways to perform arithmetic on dates. This includes adding or subtracting a year (or many years) from a given date.

Here’s an example of subtracting a year from a date in MariaDB.

The DATE_SUB() Function

The DATE_SUB() function allows you to subtract a certain number of units from a date. Therefore you can return the date, minus a number of days, weeks, months, years, etc.

In our case, we want to subtract a year.

Example:

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

Result:

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

Multiple Years

If you need to subtract more than one year, just use that number instead of 1. However, the YEAR keyword remains non-plural regardless:

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

Result:

+-----------------------------------------+
| DATE_SUB('2021-05-01', INTERVAL 2 YEAR) |
+-----------------------------------------+
| 2019-05-01                              |
+-----------------------------------------+

The Subtraction Operator (-)

Another way to subtract a year from a date is to use the subtraction operator (-), also known as the minus operator.

Example:

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

Result:

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

The Addition Operator (+)

You can alternatively use the addition operator (+) along with a negative amount.

Example:

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

Result:

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

Other Ways to Subtract Years from Dates

Here are some other approaches you could use to subtract one or more years from a date:

  • The SUBDATE() function (this is a synonym for DATE_SUB() when used with the same syntax).
  • The DATE_ADD() function (providing a negative amount will subtract that amount from the date).
  • The ADDDATE() function (providing a negative amount will subtract that amount from the date).