The MySQL DATE_ADD() function explained

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.