SUBDATE() function in MySQL

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