The MySQL DATE_ADD() function explained

Learn how the MySQL DATE_ADD function works here

Posted on October 21, 2021


The MySQL DATE_ADD() function is used to add a time value into a date expression.

The syntax of the function is as shown below:

DATE_ADD([date], INTERVAL [expression] [unit])

The DATE_ADD() function accepts two parameters:

  • date - the first parameter where you put the date expression you want to add time value to
  • INTERVAL - the second parameter where you put the time value to the date

For example, here’s how to add one day to 18th October 2021:

SELECT DATE_ADD('2021-10-18', INTERVAL 1 DAY);

The returned result will be 19th October 2021 as shown below:

+----------------------------------------+
| DATE_ADD('2021-10-18', INTERVAL 1 DAY) |
+----------------------------------------+
| 2021-10-19                             |
+----------------------------------------+

The INTERVAL is a keyword from MySQL to indicate a Temporal Interval or a period of time value.

The available unit types for the INTERVAL keyword are as follows:

  • 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

Some unit types expect an expression in a specific format. You can see the list of expression formats expected for each unit in Temporal Interval documentation here.

Let’s see another example of adding an interval to a date using the DATE_ADD function.

The following query will add 5 years and 2 months to the date 14th October 2011:

SELECT DATE_ADD('2011-10-14', INTERVAL '5-2' YEAR_MONTH);

--   +---------------------------------------------------+
--   | DATE_ADD('2011-10-14', INTERVAL '5-2' YEAR_MONTH) |
--   +---------------------------------------------------+
--   | 2016-12-14                                        |
--   +---------------------------------------------------+

The expression required for the YEAR_MONTH unit type is 'YEARS-MONTHS' as shown in the documentation.

You can also add some hours and minutes values to a date as follows:

SELECT DATE_ADD('2011-10-14', INTERVAL '2:45' HOUR_MINUTE);

--   +-----------------------------------------------------+
--   | DATE_ADD('2011-10-14', INTERVAL '2:45' HOUR_MINUTE) |
--   +-----------------------------------------------------+
--   | 2011-10-14 02:45:00                                 |
--   +-----------------------------------------------------+

And that’s how the DATE_ADD() function works. It can accept any valid datetime format like DATE or DATETIME as the first parameter.

Related articles:

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.