MariaDB ADD_MONTHS() Function

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

MariaDB ADD_MONTHS() Syntax

Here is the syntax of the MariaDB ADD_MONTHS() function:

ADD_MONTHS(date, months)

Parameters

date

Required. It can be a date, datetime, or timestamp value.

months

Required. The number of months to add.

If you provide no parameters or the wrong number of parameters, MariaDB will report an error: 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.

Return value

The MariaDB ADD_MONTHS() function returns a result with the given month added to the given date.

If any argument number is NULL, the ADD_MONTHS() function will return NULL.

MariaDB ADD_MONTHS() Examples

Example 1 - Add months

To add 5 months to 2023-01-05, use the following statement:

SELECT ADD_MONTHS('2023-01-05', 5);

Output:

+-----------------------------+
| ADD_MONTHS('2023-01-05', 5) |
+-----------------------------+
| 2023-06-05                  |
+-----------------------------+

Example 2 - Subtract months

To subtract 5 months from 2023-01-05, use the following statement:

SELECT ADD_MONTHS('2023-01-05', -5);

Output:

+------------------------------+
| ADD_MONTHS('2023-01-05', -5) |
+------------------------------+
| 2022-08-05                   |
+------------------------------+

In this example, we provide a negative number to subtract the specified months from the specified date.

Example 3 - Numeric Date

MariaDB ADD_MONTHS() allows you to provide dates as numbers:

SELECT ADD_MONTHS(20230105, 5);

Output:

+-------------------------+
| ADD_MONTHS(20230105, 5) |
+-------------------------+
| 2023-06-05              |
+-------------------------+

Example 4 - Datetime

MariaDB ADD_MONTHS() allows you to provide a date and time value:

SELECT ADD_MONTHS('2023-01-05 10:11:12', 5);

Output:

+--------------------------------------+
| ADD_MONTHS('2023-01-05 10:11:12', 5) |
+--------------------------------------+
| 2023-06-05 10:11:12                  |
+--------------------------------------+

Conclusion

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

The MariaDB ADD_MONTHS() function were introduced in MariaDB 10.6.1 to enhance Oracle compatibility. There are at least 6 ways to add months to dates in MariaDB.