Tutorial Gateway

MySQL SUBDATE Function

by

MySQL SUBDATE function is one of the Date Functions, which is a synonym of the DATE_SUB function. This MySQL 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);

OUTPUT

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 MySQL 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);

OUTPUT

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);

OUTPUT

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);

OUTPUT

MySQL SUBDATE Function 4