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