
        
        
            
                
                
                    SELECT ADDDATE(date, INTERVAL expr unit);
SELECT ADDDATE(date, days);

Parameters

1. date | date/datetime

The date or datetime to add the time interval to.

2. days | integer

The number of days to add to the provided date.

3. expr unit | integer with units

The interval to add to the provided date together with its units.

Refer to 'List of units' at the bottom of page for a valid list of units.

WARNING

Either the days or expr unit parameter must be specified, they cannot be used together.

Return value

The result of adding the specified interval to date.

Examples

Consider the following table about some students:

student_id	fname	lname	day_enrolled	age	username
1	Sky	Towner	2015-12-03	17	stowner1
2	Ben	Davis	2016-04-20	19	bdavis2
3	Travis	Apple	2018-08-14	18	tapple3
4	Arthur	David	2016-04-01	16	adavid4
5	Benjamin	Town	2014-01-01	17	btown5

The above sample table can be created using the code here.

Basic usage

To add 2 days to student enrollment dates:


        
        
            
                
                
                    SELECT fname, day_enrolled, ADDDATE(day_enrolled, 2)
FROM students;
                
            
            +----------+--------------+--------------------------+
| fname    | day_enrolled | ADDDATE(day_enrolled, 2) |
+----------+--------------+--------------------------+
| Sky      | 2015-12-03   | 2015-12-05               |
| Ben      | 2016-04-20   | 2016-04-22               |
| Travis   | 2018-08-14   | 2018-08-16               |
| Arthur   | 2016-04-01   | 2016-04-03               |
| Benjamin | 2014-01-01   | 2014-01-03               |
+----------+--------------+--------------------------+

To add 2 months to student enrollment dates:


        
        
            
                
                
                    SELECT fname, day_enrolled, ADDDATE(day_enrolled, INTERVAL 2 MONTH)
FROM students;
                
            
            +----------+--------------+-----------------------------------------+
| fname    | day_enrolled | ADDDATE(day_enrolled, INTERVAL 2 MONTH) |
+----------+--------------+-----------------------------------------+
| Sky      | 2015-12-03   | 2016-02-03                              |
| Ben      | 2016-04-20   | 2016-06-20                              |
| Travis   | 2018-08-14   | 2018-10-14                              |
| Arthur   | 2016-04-01   | 2016-06-01                              |
| Benjamin | 2014-01-01   | 2014-03-01                              |
+----------+--------------+-----------------------------------------+

Negative intervals

To subtract 1 day from a date:


        
        
            
                
                
                    SELECT ADDDATE('2020-01-01', -1);
                
            
            +---------------------------+
| ADDDATE('2020-01-01', -1) |
+---------------------------+
| 2019-12-31                |
+---------------------------+

Note that there is a separate method SUBDATE(~) specifically for subtracting intervals from a date / datetime.

List of units

The below table represents a list of units that are supported:

Unit	Expected Input 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'`

Published by Arthur Yanagisawa

Edited by 0 others

Did you find this page useful?

thumb_up

thumb_down

Comment

Citation

Ask a question or leave a feedback...

Official MySQL Documentation

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_adddate

thumb_up

thumb_down

chat_bubble_outline

settings

Enjoy our search

Hit / to insta-search docs and recipes!