Tutorial Gateway

MySQL SUBDATE Function

by

MySQL SUBDATE function is the one of the Date Function, which is a synonym of the DATE_SUB function. This function is used to subtract the user specified intervals from the given date, and returns the date and time.

In this article, we will show you, How to use this MySQL SUBDATE function to subtract certain intervals from the given date expression with an example.

MySQL SUBDATE Syntax

The basic syntax of the SUBDATE() Function in MySQL is as shown below:

SUBDATE(Date, INTERVAL expression Unit);

SUBDATE(Date expression, days);

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

MySQL SUBDATE function Example

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

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
  • 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

Thank You for Visiting Our Blog