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 |