In MySQL, you can use the SUBDATE() function to subtract a specified amount of time from a date. For example, you could use it to subtract 10 days from a given date. You can specify whether to subtract days, weeks, months, quarters, years, etc. You can also subtract a time value, such as seconds, microseconds, etc.

When using the first syntax listed below, the SUBDATE() function is a synonym for the DATE_SUB() function (similar to how ADDDATE() is a synonym for DATE_ADD() when using the same syntax).

Syntax

You can use this function in the following two ways:

SUBDATE(date,INTERVAL expr unit)

Or

SUBDATE(expr,days)

Example 1 – The First Syntax

Here’s an example of using the first form of the syntax.

SELECT SUBDATE('2018-05-10', INTERVAL 2 DAY) AS Result;

Result:

+------------+
| Result     |
+------------+
| 2018-05-08 |
+------------+

This example subtracts 2 days from the date supplied by the first argument.

Example 2 – The Second Syntax

This example could be rewritten as the following:

SELECT SUBDATE('2018-05-10', 2) AS Result;

Result:

+------------+
| Result     |
+------------+
| 2018-05-08 |
+------------+

This uses the second form of the syntax. The second argument is an integer that represents how many days should be subtracted from the date supplied by the first argument.

As mentioned, SUBDATE() is a synonym for DATE_SUB(), but only when the first syntax is being used. The second syntax is only available in SUBDATE().

Example 3 – Other Date Units

One benefit of the first form of the syntax is that you can specify whether to subtract days, weeks, months, years, etc. Here are some examples.

SELECT 
    '2018-05-10' AS 'Start Date',
    SUBDATE('2018-05-10', INTERVAL 2 WEEK) AS '-2 Weeks',
    SUBDATE('2018-05-10', INTERVAL 2 MONTH) AS '-2 Months',
    SUBDATE('2018-05-10', INTERVAL 2 QUARTER) AS '-2 Quarters',
    SUBDATE('2018-05-10', INTERVAL 2 YEAR) AS '-2 Years';

Result:

+------------+------------+------------+-------------+------------+
| Start Date | -2 Weeks   | -2 Months  | -2 Quarters | -2 Years   |
+------------+------------+------------+-------------+------------+
| 2018-05-10 | 2018-04-26 | 2018-03-10 | 2017-11-10  | 2016-05-10 |
+------------+------------+------------+-------------+------------+

Example 4 – Time Units

You can also use SUBDATE() to subtract time units from a date/time value. Here’s an example.

SELECT SUBDATE('2018-05-10 01:00:00', INTERVAL 2 HOUR) AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2018-05-09 23:00:00 |
+---------------------+

Expected Values

The following table shows the valid unit values and their expected format.

unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND	‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
HOUR_MICROSECOND	‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
DAY_MICROSECOND	‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_HOUR	‘DAYS HOURS’
YEAR_MONTH	‘YEARS-MONTHS’