SQL ADDDATE() is MySQL function used to add/subtract a time interval to a date expression.
Note: In MS SQL Server, SQL ADDDATE() is the similar function.
SQL ADDDATE Syntax
ADDDATE(date-expression, INTERVAL expr type)
Or we can use the simpler syntax for adding days:
ADDDATE(date-expression, days)
type can be one of the following:
| type Value | Expected expr Format | Example |
|---|---|---|
| YEAR_MONTH | ‘YEARS-MONTHS’ | INTERVAL ‘2-3’ YEAR_MONTH |
| DAY_HOUR | ‘DAYS HOURS’ | INTERVAL ‘2 12’ DAY_HOUR |
| DAY_MINUTE | ‘DAYS HOURS:MINUTES’ | INTERVAL ‘1 2:20’ DAY_HOUR |
| DAY_SECOND | ‘DAYS HOURS:MINUTES:SECONDS’ | INTERVAL ‘1 2:20:15’ DAY_SECOND |
| DAY_MICROSECOND | ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’ | INTERVAL ‘1 2:20:15.78’ DAY_MICROSECOND |
| HOUR_MINUTE | ‘HOURS:MINUTES’ | INTERVAL ‘2:12’ HOUR_MINUTE |
| HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ | INTERVAL ‘2:12:4’ HOUR_SECOND |
| HOUR_MICROSECOND | ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ | INTERVAL ‘2:12:4.566’ HOUR_MICROSECOND |
| MINUTE_SECOND | ‘MINUTES:SECONDS’ | INTERVAL ’12:24′ MINUTE_SECOND |
| MINUTE_MICROSECOND | ‘MINUTES:SECONDS.MICROSECONDS’ | INTERVAL ’12:24.756′ MINUTE_MICROSECOND |
| SECOND_MICROSECOND | ‘SECONDS.MICROSECONDS’ | INTERVAL ‘24.756’ SECOND_MICROSECOND |
| YEAR | YEARS | INTERVAL 2 YEAR |
| QUARTER | QUARTERS | INTERVAL 2 QUARTER |
| MONTH | MONTHS | INTERVAL 2 MONTH |
| WEEK | WEEKS | INTERVAL 2 WEEK |
| DAY | DAYS | INTERVAL 2 DAY |
| HOUR | HOURS | INTERVAL 2 HOUR |
| MINUTE | MINUTES | INTERVAL 2 MINUTE |
| SECOND | SECONDS | INTERVAL 2 SECOND |
| MICROSECOND | MICROSECONDS | INTERVAL 2 MICROSECOND |
We can also write in shorter syntax:
[Date Expression] + INTERVAL expr type [Date Expression] - INTERVAL expr type
SQL ADDDATE Examples
Add 3 days to a date expression:
SELECT ADDDATE('2011-09-11', INTERVAL 3 DAY) AS Add3Days
OR:
SELECT ADDDATE('2011-09-11',3) AS Add3Days
OR:
SELECT '2011-09-11' + INTERVAL 3 DAY AS Add3Days
They will all produce the same result:
| Add3Days |
|---|
| 2011-09-14 |
Add 2 hours and 10 minutes to a date expression:
SELECT ADDDATE('2011-09-11 2:42:36', INTERVAL '2:10' HOUR_MINUTE) AS NewDate
OR:
SELECT '2011-09-11 2:42:36' + INTERVAL '2:10' HOUR_MINUTE AS NewDate
They both will have the same result:
| NewDate |
|---|
| 2011-09-11 04:52:36 |
Subtract 2 hours and 10 minutes to a date expression:
SELECT ADDDATE('2011-09-11 2:42:36', INTERVAL '-2:10' HOUR_MINUTE) AS NewDate
OR:
SELECT'2011-09-11 2:42:36' - INTERVAL '2:10' HOUR_MINUTE AS NewDate
The result will look like:
| NewDate |
|---|
| 2011-09-11 00:32:36 |