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

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

Bonus update: I’ve now added a 7th way to add a month to a date at the end of this article. So I guess it’s now 7 ways to add a month 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 MONTH);

Result:

+------------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 1 MONTH) |
+------------------------------------------+
| 2021-06-01                               |
+------------------------------------------+

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

SELECT DATE_ADD('2021-05-01', INTERVAL 2 MONTH);

Result:

+------------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 2 MONTH) |
+------------------------------------------+
| 2021-07-01                               |
+------------------------------------------+

It’s also possible to use another date/time unit with the appropriate number of that unit. Therefore you could specify a certain number of days, such as 30 or 31.

Example:

SELECT DATE_ADD('2021-05-01', INTERVAL 31 DAY);

Result:

+-----------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 31 DAY) |
+-----------------------------------------+
| 2021-06-01                              |
+-----------------------------------------+

When using days, whether it works out to be exactly a month or not will depend on the month in question. In the above example I could have provided 30 days, but that would have kept us within the same month:

SELECT DATE_ADD('2021-05-01', INTERVAL 30 DAY);

Result:

+-----------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 30 DAY) |
+-----------------------------------------+
| 2021-05-31                              |
+-----------------------------------------+

So if you’re actually wanting to add a month, then the MONTH unit is much easier than trying to work out how many days to add.

The ADDDATE() Function

Another way to add a month 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 MONTH);

Result:

+-----------------------------------------+
| ADDDATE('2021-05-01', INTERVAL 1 MONTH) |
+-----------------------------------------+
| 2021-06-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 MONTH);

Result:

+-------------------------------------------+
| DATE_SUB('2021-05-01', INTERVAL -1 MONTH) |
+-------------------------------------------+
| 2021-06-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 MONTH);

Result:

+------------------------------------------+
| SUBDATE('2021-05-01', INTERVAL -1 MONTH) |
+------------------------------------------+
| 2021-06-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 months to our date.

Example:

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

Result:

+---------------------------------+
| '2021-05-01' + INTERVAL 1 MONTH |
+---------------------------------+
| 2021-06-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 MONTH;

Result:

+----------------------------------+
| '2021-05-01' - INTERVAL -1 MONTH |
+----------------------------------+
| 2021-06-01                       |
+----------------------------------+

Bonus: The ADD_MONTHS() Function

Since I first wrote this article, a new function has been introduced into MariaDB specifically for adding months to a date.

The function is called ADD_MONTHS(), and it was introduced in MariaDB 10.6.1 to enhance Oracle compatibility.

So I guess this article should be called 7 Ways to Add a Month to a Date… 🙂

Example:

SELECT ADD_MONTHS('2020-01-01', 3);

Result:

+-----------------------------+
| ADD_MONTHS('2020-01-01', 3) |
+-----------------------------+
| 2020-04-01                  |
+-----------------------------+

Other Date and Time Units

The above examples add a number of months 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.