DATE_SUB() Examples

In MySQL, you can use the DATE_SUB() function to subtract a specified amount of time from a date. For example, you can use it to subtract 7 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.

This function is similar to DATE_ADD(), except that it subtracts from a date instead of adds to it.

Syntax

The syntax goes like this:

DATE_SUB(date,INTERVAL expr unit)

Example 1 – Basic Usage

Here’s an example of usage.

SELECT DATE_SUB('2021-05-07', INTERVAL 5 DAY) AS Result;

Result:

+------------+
| Result     |
+------------+
| 2021-05-02 |
+------------+

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

Example 2 – Other Date Units

You can specify the units in days, weeks, months, years, etc. Here are some examples.

SELECT 
    '2021-05-07' AS 'Start Date',
    DATE_SUB('2021-05-07', INTERVAL 2 WEEK) AS '-2 Weeks',
    DATE_SUB('2021-05-07', INTERVAL 2 MONTH) AS '-2 Months',
    DATE_SUB('2021-05-07', INTERVAL 2 QUARTER) AS '-2 Quarters',
    DATE_SUB('2021-05-07', INTERVAL 2 YEAR) AS '-2 Years';

Result:

+------------+------------+------------+-------------+------------+
| Start Date | -2 Weeks   | -2 Months  | -2 Quarters | -2 Years   |
+------------+------------+------------+-------------+------------+
| 2021-05-07 | 2021-04-23 | 2021-03-07 | 2020-11-07  | 2019-05-07 |
+------------+------------+------------+-------------+------------+

Example 3 – Time Units

You can also subtract time units from a date/time value. Here’s an example.

SELECT DATE_SUB('2021-05-07 10:00:00', INTERVAL 5 HOUR) AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2021-05-07 05:00:00 |
+---------------------+

And you can specify multiple units at the same time. For example, you can specify hours and minutes. Like this.

SELECT DATE_SUB('2021-05-07 10:00:00', INTERVAL '1:30' HOUR_MINUTE) AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2021-05-07 08:30: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’

You can also use the SUBDATE() function to do the same thing (it’s a synonym for the DATE_SUB() function when using the same syntax).

In addition, the syntax of SUBDATE() has a second form, which is a shorthand method of subtracting a certain number of days from a date. For more info, see SUBDATE() Examples in MySQL.