How to Use the MySQL DATE_ADD() Function
In this tutorial, you’ll learn how to use the MySQL DATE_ADD() function. The DATE_ADD() function adds a time and date interval to a given date and then returns a new date.
MySQL DATE_ADD() Syntax
The syntax of the DATE_ADD() function is as follows:
DATE_ADD(date, INTERVAL expression unit)
Arguments
- The date, expression, and unit are required.
- The unit argument is a type of interval to add. It can be one of the followings:
- MINUTE
- HOUR
- SECOND
- DAY
- WEEK
- QUARTER
- HOUR_MINUTE
- HOUR_SECOND
- MICROSECOND
- SECOND_MICROSECOND
- MINUTE_MICROSECOND
- MINUTE_SECOND
- HOUR_MICROSECOND
- DAY_MICROSECOND
- DAY_SECOND
- DAY_MINUTE
- DAY_HOUR
- YEAR_MONTH
Examples
Adding hours
The following statement adds 2 hours to 2019-01-10 and returns 2019-01-10 02:00:00:
SELECT DATE_ADD("2019-01-01",INTERVAL 2 HOUR);
Adding seconds
The following statement adds 3 seconds to 2019-01-01 and returns 2019-01-01 00:00:03:
SELECT DATE_ADD("2019-01-01", INTERVAL 3 SECOND);
Adding months
The following statement adds three months to 2019-01-01 and returns 2019-04-01:
SELECT DATE_ADD("2019-01-01", INTERVAL 3 MONTH);
Adding years
The following statement adds one year to 2019-01-01 and returns 2020-01-01:
SELECT DATE_ADD("2019-01-01", INTERVAL 1 YEAR);
Adding weeks
The following statement adds four weeks to 2019-01-01 and returns 2019-01-29:
SELECT DATE_ADD("2019-01-01", INTERVAL 4 WEEK);
Adding hours and minutes
The following statement adds 2 hours and 20 minutes to 2019-01-01 and returns 2019-01-01 02:20:00:
SELECT DATE_ADD("2019-01-01", INTERVAL "2:20" HOUR_MINUTE);
Adding years and months
The following statement adds one year and five months to 2019-01-01 and returns 2020-06-01:
SELECT DATE_ADD("2019-01-01", INTERVAL "1:5" YEAR_MONTH);
In this tutorial, you’ve learned how to use the MySQL DATE_ADD() function. DATE_ADD() is a function to add a date and time interval to a given date and then return a new date. If you need to subtract a time and date interval from a date, use the DATE_SUB() function.