MySQL DATE_ADD() Function with Examples

How to Use the MySQL DATE_ADD() Function

This tutorial explains how to use MySQL DATE_ADD() function, which adds a time or date interval to a given date and returns a new date.

To subtract a time or date interval from a given time, use the DATE_SUB() function.

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-10",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 have learned how to use the MySQL DATE_ADD() function. DATE_ADD() is a function that adds a date and time interval to a given date and returns a new date. If you want to subtract the time and date interval from the date, use the DATA_SUB() function.