SQL ADDDATE

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>