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 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 have to specify the number of Units or Values to minus. For example, 2 MONTH means subtract two months from a given.
  • 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 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);
MySQL DATE_SUB Function 1

DATE_SUB Function Example 2

In this MySQL DATE_SUB function 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);
DATE SUB With INTERVAL Example 2

DATE SUB Function Example 3

In this 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