MySQL SUBDATE Function

MySQL SUBDATE is one of the Date Functions, which is a synonym of the DATE_SUB. 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 MySQL SUBDATE Function 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 method. Here, we are subtracting 8 Microseconds, 8 Seconds, and 8 Minutes from the given DateTime expression.

TIP: I suggest you refer to the Date methods 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);
SUBTRACT DATES Example 1

In this example, we are subtracting 8 Hours, and 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);
SUBDATE Example 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);
SUBDATE Example 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. Refer DATE_SUB
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

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.