MySQL DATE_SUB Function

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

MySQL DATE_SUB Syntax

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

DATE_SUB(Date, INTERVAL expression Unit);
  • Date: It accepts either DateTime value or string date.
  • Expression: You must specify the number of Units or Values to minus. For example, 2 MONTH means subtracting two months from a given.
  • Unit: It accepts unit values ranging 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 method.

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 method 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);
Subtract Dates Example 1

DATE_SUB Function Example 2

In this MySQL DATE_SUB function example, First, we subtracted 420 Hours. We subtracted 120 days from a given Datetime expression within the next statement. 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 With INTERVAL Example 2

DATE SUB Function Example 3

In this example, we remove or subtract 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);
Date sub Example 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