Open In App

SUBDATE() function in MySQL

Last Updated : 27 Nov, 2020
Comments
Improve
Suggest changes
Like Article
Like
Report
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

Next Article
Article Tags :

Similar Reads