DATE_ADD() Examples

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.