MariaDB provides several ways to add a certain number of days to a given date. For example, you might want to add 10 days to a date, or 30 days, or even just 1 day. Fortunately, MariaDB makes this easy.

Here are 8 ways to add a number of days to a given date in MariaDB.

The ADDDATE() Function

Let’s start with the ADDDATE() function.

This function has two different syntaxes. The first syntax provides a concise way of adding a certain number of days to a date.

Example:

SELECT ADDDATE('2021-05-01', 10);

Result:

+---------------------------+
| ADDDATE('2021-05-01', 10) |
+---------------------------+
| 2021-05-11                |
+---------------------------+

Using this syntax, we provide two arguments. The first argument is the date, and the second is the number of days to add to that date.

The ADDDATE() Function – Syntax 2

The ADDDATE() function also has a more verbose syntax. This syntax allows us to be more explicit in how we add the days to the date.

Example:

SELECT ADDDATE('2021-05-01', INTERVAL 10 DAY);

Result:

+----------------------------------------+
| ADDDATE('2021-05-01', INTERVAL 10 DAY) |
+----------------------------------------+
| 2021-05-11                             |
+----------------------------------------+

In this case, we use the INTERVAL keyword, followed by the number of days, followed by the DAY keyword.

The INTERVAL keyword is used to add or subtract a time interval of time to a DATETIME, DATE or TIME value. We can use it on dates, along with the applicable date/time unit to add a certain number of days to our date. In our case, the date/time unit is DAY, but it could just as easily be MONTH, YEAR, HOUR, etc, as well as a number of composite units.

The DATE_ADD() Function

The DATE_ADD() function works the same as ADDDATE() when using the verbose syntax. In fact, when using the verbose syntax, ADDDATE() is a synonym for DATE_ADD().

Example:

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

Result:

+-----------------------------------------+
| DATE_ADD('2021-05-01', INTERVAL 10 DAY) |
+-----------------------------------------+
| 2021-05-11                              |
+-----------------------------------------+

The SUBDATE() Function – Syntax 1

The SUBDATE() 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 SUBDATE('2021-05-01', -10);

Result:

+----------------------------+
| SUBDATE('2021-05-01', -10) |
+----------------------------+
| 2021-05-11                 |
+----------------------------+

The SUBDATE() Function – Syntax 2

The SUBDATE() function also has the more verbose syntax (similar to the ADDDATE() function).

Example:

SELECT SUBDATE('2021-05-01', INTERVAL -10 DAY);

Result:

+-----------------------------------------+
| SUBDATE('2021-05-01', INTERVAL -10 DAY) |
+-----------------------------------------+
| 2021-05-11                              |
+-----------------------------------------+

The DATE_SUB() Function

Here’s an example that uses the DATE_SUB() function:

SELECT DATE_SUB('2021-05-01', INTERVAL -10 DAY);

Result:

+------------------------------------------+
| DATE_SUB('2021-05-01', INTERVAL -10 DAY) |
+------------------------------------------+
| 2021-05-11                               |
+------------------------------------------+

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 days to our date.

Example:

SELECT '2021-05-01' + INTERVAL 10 DAY;

Result:

+--------------------------------+
| '2021-05-01' + INTERVAL 10 DAY |
+--------------------------------+
| 2021-05-11                     |
+--------------------------------+

The - Operator

The - operator can be used to subtract an amount from a date. However, if you subtract a negative number, then a positive amount is added to the date:

SELECT '2021-05-01' - INTERVAL -10 DAY;

Result:

+---------------------------------+
| '2021-05-01' - INTERVAL -10 DAY |
+---------------------------------+
| 2021-05-11                      |
+---------------------------------+

BONUS: The ADDTIME() Function

I said this article contains 8 ways to add days to a date, but here’s a couple of bonus functions! Maybe I should have called this article “10 ways to add days to a datetime value” instead.

Anyway, you can use the ADDTIME() function to add a number of days to a datetime expression.

Example:

SELECT ADDTIME('2021-05-01 10:30:45', '1 0:0:0');

Result:

+-------------------------------------------+
| ADDTIME('2021-05-01 10:30:45', '1 0:0:0') |
+-------------------------------------------+
| 2021-05-02 10:30:45                       |
+-------------------------------------------+

You can also change the other time units, such as hours, minutes, seconds, etc.

You can alternatively switch it around and use SUBTIME() with a negative value if you so wish:

SELECT SUBTIME('2021-05-01 10:30:45', '-1 0:0:0');

Result:

+--------------------------------------------+
| SUBTIME('2021-05-01 10:30:45', '-1 0:0:0') |
+--------------------------------------------+
| 2021-05-02 10:30:45                        |
+--------------------------------------------+

Other Date and Time Units

The above examples add a number of days to the date, but we can use the same techniques to add any date/time unit. We use DAY as the date/time unit, but as mentioned, it could just as easily be MONTH, YEAR, HOUR, etc, as well as any of the composite units.

See MariaDB Date and Time Units for a full list of date/time units that can be used with the above functions and operator.