MySQL DATE_ADD() Function with Examples

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.