MySQL DATE_SUB Function

MySQL DATE_SUB function is one of the Date Function, which is useful to remove or subtract the user-specified intervals from a given date and returns the date or DateTime.

MySQL DATE_SUB Syntax

The basic syntax of the DATE_SUB() Function in MySQL is as shown below:

DATE_SUB(Date, INTERVAL expression Unit);
  • Date: It accepts either DateTime value or string date.
  • Expression: You have to specify the number of Units or Value to subtract. For example, 2 MONTH means subtract two months from a given date.
  • Unit: It accepts unit values range from Microsecond to Year

MySQL DATE_SUB function Examples

The following are the list examples that help you understand the use of this Date Sub function in MySQL.

DATE_SUB Function Example 1

Here, we subtracted 1000 Microseconds, 250 Seconds, and 720 Minutes from a given DateTime (2019-02-10 10:15:20.000010). I suggest you refer to the Date Function article in MySQL to understand the Units after the Interval

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 1000 MICROSECOND);

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 250 SECOND);

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 720 MINUTE);
MySQL DATE_SUB Function 1

MySQL DATE_SUB Function Example 2

In this MySQL DATE SUB example, First, we subtracted 420 Hours. Within the next statement, we subtracted 120 days from a given Datetime expression. Within the third statement, we removed 12 Weeks.

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 420 HOUR);

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 120 DAY);

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 12 WEEK);
MySQL DATE_SUB Function 2

MySQL DATE SUB Function Example 3

In this MySQL DATE SUB function example, we are removing or subtracting 33 Months, 12 Quarters, and 16 Years from a given Datetime expression.

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 33 MONTH);

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 12 QUARTER);

SELECT DATE_SUB('2019-02-10 10:15:20.000010', INTERVAL 16 YEAR);
MySQL DATE_SUB Function 3

MySQL DATE_SUB Function Example 4

Here, First, we subtracted 16 Years and 14 Months. The second statement subtracts 31 Days, 18 Hours, and 25 Minutes. The third statement removes 22 Hours and 40 Minutes from a given DateTime.

SELECT DATE_SUB('2019-02-10 10:15:20', INTERVAL '16-14' YEAR_MONTH);

SELECT DATE_SUB('2019-02-10 10:15:20', INTERVAL '31 18:25' DAY_MINUTE);

SELECT DATE_SUB('2019-02-10 10:15:20', INTERVAL '22:40' HOUR_MINUTE);
MySQL DATE_SUB Function 4