MySQL DATE_SUB(): Subtract Days from Date or DateTime

MySQL DATE_SUB() function; In this tutorial, we would love to share with you how to subtract days from date or datetime using MySQL date_sub() function with the help of examples.

MySQL DATE_SUB() Function

In MySQL, the DATE_SUB () function is used to subtract(decrease) a specified time from a given date.

Example:- You want to subtract(decrease) 15 days from a given date. You can also to subtract day, week, month, quarter, year from a given date. You can also subtract(decrease) a time value, such as seconds, microseconds, etc.

Syntax

The basic syntax of MySQL DATE_SUB() function is:

DATE_SUB(date,INTERVAL expr unit)

Example-1

Here, we will take the first simple example for demonstration of this function is:

SELECT DATE_SUB('2019-07-23', INTERVAL 6 DAY) AS Result;

Output-1

+------------+
| Result     |
+------------+
| 2019-07-17 |
+------------+

In the above example, we have subtracted the 6 days from the given date.

Example-2

We take the next example of this function with curdate() function of MySQL. In this example, we will subtract the days, weeks, months, years’ in the current date.

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

Output-2

 +------------+------------+------------+-------------+------------+
 | Start Date | -2 Weeks   | -2 Months  | -2 Quarters | -2 Years   |
 +------------+------------+------------+-------------+------------+
 | 2019-07-23 | 2019-07-09 | 2019-05-23 | 2019-01-23  |2017-07-23  |
 +------------+------------+------------+-------------+------------+

Example-3

Next, we take an example of this function with MySQL NOW() function. We will subtract the time units from the given date/time value and also subtract the time unit from the now() function. See the example below for a demonstration:

SELECT DATE_SUB('2019-07-21 10:00:00', INTERVAL 4 HOUR) AS Result,
       DATE_SUB(NOW(), INTERVAL 5 HOUR) AS NOW;

Output-3

 +---------------------+---------------------+
 | Result              | NOW                 |
 +---------------------+---------------------+
 | 2019-07-23 06:00:00 | 2019-07-22 22:04:40 |
 +---------------------+---------------------+

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’

Conclusion

Here, you have learned how to use MySQL date_sub() function with various examples.

Subscribe to Blog via Email