Subtract a day from a date value in MariaDB

In MariaDB, to subtract one or more days from a date, you can use the SUBDATE() function or the subtraction operator.

Some examples are given below.

Example 1: SUBDATE() Function

The following statement subtracts one day from a specified date using SUBDATE():

SELECT SUBDATE('2022-12-13', 1);

Output:

+--------------------------+
| SUBDATE('2022-12-13', 1) |
+--------------------------+
| 2022-12-12               |
+--------------------------+

Here, SUBDATE() accepted two parameters, the first parameter is a date value, and the second parameter is the number of days to subtract.

If you need to subtract some days, just give it directly through the second parameter. For example, if you need to calculate the date 5 days ago:

SELECT SUBDATE(CURDATE(), 5);

Output:

+-----------------------+
| SUBDATE(CURDATE(), 5) |
+-----------------------+
| 2022-12-08            |
+-----------------------+

Example 2: INTERVAL value

The second parameter of the SUBDATE() function supports receiving a value of INTERVAL type, for example, to subtract a 10 days, you can use INTERVAL 10 DAY.

The following statement calculates the date 10 days ago:

SELECT SUBDATE(CURDATE(), INTERVAL 10 DAY);

Output:

+-------------------------------------+
| SUBDATE(CURDATE(), INTERVAL 10 DAY) |
+-------------------------------------+
| 2022-12-03                          |
+-------------------------------------+

Example 3: Using the subtraction operator -

The following statement implements subtracting 1 day from '2022-12-23':

SELECT '2022-12-23' - INTERVAL 1 DAY;

Output:

+-------------------------------+
| '2022-12-23' - INTERVAL 1 DAY |
+-------------------------------+
| 2022-12-22                    |
+-------------------------------+

In this example, the date value is given by a format of type String, and the number of days is given by data of INTERVAL type. If the date value is a date, the number of days can be given directly as a number. See the example below:

SELECT CURDATE(), CURDATE() - 1;

Output:

+------------+---------------+
| CURDATE()  | CURDATE() - 1 |
+------------+---------------+
| 2022-12-13 |      20221212 |
+------------+---------------+

Example 4: Addition operator (+)

MariaDB allows you to perform calculations on +.

To subtract one day from a date, use the addition operator + and a negative number, as follows:

SELECT '2022-12-23' + INTERVAL -1 DAY;

Output:

+--------------------------------+
| '2022-12-23' + INTERVAL -1 DAY |
+--------------------------------+
| 2022-12-22                     |
+--------------------------------+

The following statement implements subtracting 60 days from '2022-12-23':

SELECT '2022-12-23' + INTERVAL -60 DAY;

Output:

+---------------------------------+
| '2022-12-23' + INTERVAL -60 DAY |
+---------------------------------+
| 2022-10-24                      |
+---------------------------------+

Other functions

In MariaDB, there are also several functions that can perform calculations on dates:

• DATE_SUB()
• DATE_ADD()
• ADDDATE()

Conclusion

This article discusses several ways to subtract a given number of days from a given date.