How to Use the DATE_SUB() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the DATE_SUB() function in MySQL.

Using the DATE_SUB() function in MySQL

The DATE_SUB() function in MySQL is used to subtract a specified interval from a date. It can be used to subtract seconds, minutes, hours, days, weeks, months, or years from a date. This function is useful when you need to calculate a past date or time based on a specific interval.

Syntax

DATE_SUB(date, INTERVAL value unit);

Examples

Here are some examples of using the DATE_SUB() function in MySQL:

Subtracting days from a date

To subtract a specified number of days from a date, you can use the DATE_SUB() function with the DAY interval.

Syntax
SELECT DATE_SUB(date, INTERVAL number_of_days DAY);
Example
SELECT DATE_SUB('2022-05-15', INTERVAL 7 DAY);

Output:

+-----------------------------------+
| DATE_SUB('2022-05-15', INTERVAL 7 DAY) |
+-----------------------------------+
| 2022-05-08                        |
+-----------------------------------+

In this example, the DATE_SUB() function subtracts 7 days from the date ‘2022-05-15’ to return a past date of ‘2022-05-08’.

Subtracting months from a date

To subtract a specified number of months from a date, you can use the DATE_SUB() function with the MONTH interval.

Syntax
SELECT DATE_SUB(date, INTERVAL number_of_months MONTH);
Example
SELECT DATE_SUB('2022-05-15', INTERVAL 3 MONTH);

Output:

+------------------------------------+
| DATE_SUB('2022-05-15', INTERVAL 3 MONTH) |
+------------------------------------+
| 2022-02-15                         |
+------------------------------------+

In this example, the DATE_SUB() function subtracts 3 months from the date ‘2022-05-15’ to return a past date of ‘2022-02-15’.

Subtracting years from a date

To subtract a specified number of years from a date, you can use the DATE_SUB() function with the YEAR interval.

Syntax
SELECT DATE_SUB(date, INTERVAL number_of_years YEAR);
Example
SELECT DATE_SUB('2022-05-15', INTERVAL 2 YEAR);

Output:

+----------------------------------+
| DATE_SUB('2022-05-15', INTERVAL 2 YEAR) |
+----------------------------------+
| 2020-05-15                       |
+----------------------------------+

In this example, the DATE_SUB() function subtracts 2 years from the date ‘2022-05-15’ to return a past date of ‘2020-05-15’.

Subtracting time intervals from a dateTime value

You can also use the DATE_SUB() function to subtract time intervals from a datetime value.

Syntax
SELECT DATE_SUB(datetime_value, INTERVAL value unit);
Example
SELECT DATE_SUB('2022-05-15 08:30:00', INTERVAL 2 HOUR);

Output:

+--------------------------------------------------+
| DATE_SUB('2022-05-15 08:30:00', INTERVAL 2 HOUR) |
+--------------------------------------------------+
| 2022-05-15 06:30:00                               |
+--------------------------------------------------+

In this example, the DATE_SUB() function subtracts 2 hours from the datetime value ‘2022-05-15 08:30:00’ to return a new datetime value of ‘2022-05-15 06:30:00’.

#MySQL