In MySQL, you can use the DATE_ADD() function to add a specified amount of time to a date. For example, you could use it to add 5 days to a given date. You can specify whether to add days, weeks, months, quarters, years, etc. You can also add a time value, such as seconds, microseconds, etc.

This article contains examples that demonstrate how the DATE_ADD() function works.

Syntax

The syntax goes like this:

DATE_ADD(date,INTERVAL expr unit)

Example 1 – Basic Usage

Here’s an example of usage.

SELECT DATE_ADD('2020-10-01', INTERVAL 20 DAY) AS Result;

Result:

+------------+
| Result     |
+------------+
| 2020-10-21 |
+------------+

This example adds 20 days to the date supplied by the first argument.

Example 2 – Other Date Units

You can specify the units in days, weeks, months, years, etc. Here are some examples.

SELECT 
    '2020-10-01' AS 'Start Date',
    DATE_ADD('2020-10-01', INTERVAL 2 WEEK) AS '+2 Weeks',
    DATE_ADD('2020-10-01', INTERVAL 2 MONTH) AS '+2 Months',
    DATE_ADD('2020-10-01', INTERVAL 2 QUARTER) AS '+2 Quarters',
    DATE_ADD('2020-10-01', INTERVAL 2 YEAR) AS '+2 Years';

Result:

+------------+------------+------------+-------------+------------+
| Start Date | +2 Weeks   | +2 Months  | +2 Quarters | +2 Years   |
+------------+------------+------------+-------------+------------+
| 2020-10-01 | 2020-10-15 | 2020-12-01 | 2021-04-01  | 2022-10-01 |
+------------+------------+------------+-------------+------------+

Example 3 – Time Units

You can also add time units to a date/time value. Here’s an example.

SELECT DATE_ADD('2020-10-01 01:00:00', INTERVAL 10 HOUR) AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2020-10-01 11:00:00 |
+---------------------+

And you can specify multiple units at the same time. For example, you can specify hours and minutes. Like this.

SELECT DATE_ADD('2020-10-01 01:00:00', INTERVAL '1:30' HOUR_MINUTE) AS Result;

Result:

+---------------------+
| Result              |
+---------------------+
| 2020-10-01 02:30:00 |
+---------------------+

Expected Values

The following table shows the valid unit values and their expected format.

unit Value	Expected expr Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
SECOND_MICROSECOND	‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND	‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND	‘MINUTES:SECONDS’
HOUR_MICROSECOND	‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND	‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE	‘HOURS:MINUTES’
DAY_MICROSECOND	‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND	‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE	‘DAYS HOURS:MINUTES’
DAY_HOUR	‘DAYS HOURS’
YEAR_MONTH	‘YEARS-MONTHS’

You can also use the ADDDATE() function to do the same thing (it’s a synonym for the DATE_ADD() function when using the same syntax). In addition, the syntax of ADDDATE()  has a second form, which is a shorthand method of adding a certain number of days to a date. For more info, see ADDDATE() Examples in MySQL.