MySQL SUBDATE Function

MySQL SUBDATE function is one of the Date Functions, which is a synonym of the DATE_SUB function. This SUBDATE function is used to subtract the user-specified intervals from the given date and returns the date and time. The basic syntax of the SUBDATE Function in MySQL is as shown below:

SUBDATE(Date, INTERVAL expression Unit);

SUBDATE(Date expression, days);

MySQL SUBDATE function Example

The below-shown queries help you understand the use of this SUBDATE function. Here, we are subtracting 8 Microseconds, 8 Seconds, and 8 Minutes from the given DateTime expression.

TIP: I suggest you refer to the Date Functions article to understand the Units after the Interval in MySQL.

SELECT SUBDATE('2016-05-19 11:14:34.0000010', INTERVAL 8 MICROSECOND);

SELECT SUBDATE('2016-05-19 11:14:34.0000010', INTERVAL 8 SECOND);

SELECT SUBDATE('2016-05-19 11:14:34.0000010', INTERVAL 8 MINUTE);
MySQL SUBDATE Function 1

In this example, we are subtracting 8 Hours, 6 days from the given DateTime expression. Within the third statement, we haven’t used the Interval. It means we used the second syntax of the subdate function.

SELECT SUBDATE('2019-02-09 11:14:34', INTERVAL 8 HOUR);

SELECT SUBDATE('2019-02-09 11:14:34', INTERVAL 6 DAY);

SELECT SUBDATE('2019-02-09 11:14:34', 6);
MySQL SUBDATE Function 2

In this MySQL SUBDATE function example, we are subtracting 6 Weeks, 6 Months, and 2 Quarters from the given DateTime expression.

SELECT SUBDATE('2019-02-09 11:14:34', INTERVAL 6 WEEK);

SELECT SUBDATE('2019-02-09 11:14:34', INTERVAL 6 MONTH);

SELECT SUBDATE('2019-02-09 11:14:34', INTERVAL 2 QUARTER);
MySQL SUBDATE Function 3

Here,

  • First, subtracted 4 Years
  • The second statement – subtracted 5 days and 8 hours from the given date.
  • Within the third statement, we subtracted 3 years and 8 months.
SELECT SUBDATE('2019-02-09 11:14:34', INTERVAL 4 YEAR);

SELECT SUBDATE('2019-02-09 11:14:34', INTERVAL '05 08' DAY_HOUR);

SELECT SUBDATE('2019-02-09', INTERVAL '03-08' YEAR_MONTH);
MySQL SUBDATE Function 4