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.