In MariaDB, ADD_MONTHS() is a built-in date and time function that adds a given number of months to a date, and returns the result.

The date can be a date, datetime, or timestamp value.

This function was introduced in MariaDB 10.6.1 to enhance Oracle compatibility. There are at least 6 other ways to add months to a date in MariaDB.

Syntax

The syntax goes like this:

ADD_MONTHS(date, months)

Where date is the date, and months is the number of months to add to it.

Example

Here’s an example:

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

Result:

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

Subtract Months

To subtract months from a date, use a negative value for the second argument.

Example:

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

Result:

+------------------------------+
| ADD_MONTHS('2020-01-01', -3) |
+------------------------------+
| 2019-10-01                   |
+------------------------------+

Numeric Context

The date can be provided in numeric form if required:

SELECT ADD_MONTHS(20200101, 3);

Result:

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

Invalid Dates

If the date is invalid, ADD_MONTHS() returns null with a warning:

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

Result:

+-----------------------------+
| ADD_MONTHS('2020-01-51', 3) |
+-----------------------------+
| NULL                        |
+-----------------------------+
1 row in set, 1 warning (0.003 sec)

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Incorrect datetime value: '2020-01-51' |
+---------+------+----------------------------------------+

Null Arguments

ADD_MONTHS() returns null if any argument is null:

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

Result:

+--------------------------------+
| ADD_MONTHS('2020-01-01', null) |
+--------------------------------+
| NULL                           |
+--------------------------------+

Invalid Number of Arguments

Passing an invalid number of arguments results in an error:

SELECT ADD_MONTHS(3);

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