MySQL SUBDATE(): Subtract from DateTime Values

MySQL SUBDATE(): function; In this tutorial, we would love to share with you how to use the SUBDATE() function of MySQL. How it works the function, we will take various examples for demonstration.

MySQL SUBDATE() Function

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

Example:- You want to subtract(decrease) 5 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 SUBDATE() function is:

SUBDATE(date,INTERVAL expr unit)

Example-1

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

SELECT SUBDATE('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',
 SUBDATE(CURDATE(), INTERVAL 2 WEEK) AS '-2 Weeks',
 SUBDATE(CURDATE(), INTERVAL 2 MONTH) AS '-2 Months',
 SUBDATE(CURDATE(), INTERVAL 2 QUARTER) AS '-2 Quarters',
 SUBDATE(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 SUBDATE('2019-07-21 10:00:00', INTERVAL 4 HOUR) AS Result,
       SUBDATE(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 SUBDATE() function with various examples.