SUBDATE() function in MySQL is used to subtracts a time value (as interval) from a given date.
Syntax :
SUBDATE(date, INTERVAL expr unit)
Parameter : This function accepts three parameters as given below :
date : First specified date.
expr : The value of the time/date interval to subtract.
unit : The type of interval. It Can be one of the following values :
- MICROSECOND
- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR
- SECOND_MICROSECOND
- MINUTE_MICROSECOND
- MINUTE_SECOND
- HOUR_MICROSECOND
- HOUR_SECOND
- HOUR_MINUTE
- DAY_MICROSECOND
- DAY_SECOND
- DAY_MINUTE
- DAY_HOUR
- YEAR_MONTH
Returns :
It returns the date after subtracting a time/date interval.
Example-1 :
Subtracting a interval value from a date and returning the date where date is specified in the format of YYYY-MM-DD and Interval in Days :
SELECT SUBDATE("2020-11-25", INTERVAL 30 DAY)
AS RESULTANTDATE;
Output :
| RESULTANTDATE |
|---|
| 2020-10-26 |
Example-2 :
Subtracting a interval value from a date and returning the date where date is specified in the format of YYYY-MM-DD and Interval in Negative months :
SELECT SUBDATE("2020-11-25", INTERVAL -02 MONTH)
AS RESULTANTDATE;
Output :
| RESULTANTDATE |
|---|
| 2021-01-25 |
Example-3 :
Subtracting a interval value from a date and returning the date where date is specified in the format of YYYY-MM-DD and Interval in HOUR.
SELECT SUBDATE("2020-11-25 04:12:06", INTERVAL 09 HOUR)
AS RESULTANTDATE;
Output :
| RESULTANTDATE |
|---|
| 2020-11-24 19:12:06 |
Example-4 :
Subtracting a interval value from a date and returning the date where date is specified in the format of YYYY-MM-DD and Interval in QUARTER.
SELECT SUBDATE("2020-11-25 04:12:06", INTERVAL 09 QUARTER)
AS RESULTANTDATE;
Output :
| RESULTANTDATE |
|---|
| 2018-08-25 04:12:06 |
Example 5 :
Subtracting a interval value from a date and returning the date where date is specified in the format of YYYY-MM-DD and Interval in -YEAR.
SELECT SUBDATE("2020-11-25 12:19:36", INTERVAL -01 YEAR)
AS RESULTANTDATE;
Output :
| RESULTANTDATE |
|---|
| 2021-11-25 12:19:36 |