SUBDATE() Function Examples – MySQL Tutorial

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 ValueExpected expr Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
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.

If you have any questions or thoughts to share, use the comment form below to reach us.

Note: The SUBDATE() function is a synonym for the DATE_SUB() function.

Spread the love

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *