MySQL: ADDDATE Function
This MySQL tutorial explains how to use the MySQL ADDDATE function with syntax and examples.
Description
The MySQL ADDDATE function returns a date after which a certain time/date interval has been added.
Syntax
The syntax for the ADDDATE function in MySQL is:
ADDDATE( date, INTERVAL value unit )
OR
ADDDATE( date, days )
Parameters or Arguments
- date
- The date to which the interval should be added.
- days
- The number of days to add to date (second syntax).
- value
- The value of the time/date interval that you wish to add. You can specify positive and negative values for this parameter (first syntax).
- unit
The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, and so on. It can be one of the following (first syntax):
unit Compatibility MICROSECOND 4.1.1+ SECOND 3.2.3+ MINUTE 3.2.3+ HOUR 3.2.3+ DAY 3.2.3+ WEEK 5+ MONTH 3.2.3+ QUARTER 5+ YEAR 3.2.3+ SECOND_MICROSECOND 4.1.1+ MINUTE_MICROSECOND 4.1.1+ MINUTE_SECOND 4.1.1+ HOUR_MICROSECOND 4.1.1+ HOUR_SECOND 4.1.1+ HOUR_MINUTE 3.2.3+ DAY_MICROSECOND 4.1.1+ DAY_SECOND 3.2.3+ DAY_MINUTE 3.2.3+ DAY_HOUR 3.2.3+ YEAR_MONTH 3.2.3+
Note
- If you specify an interval value that is too short for the unit that you have specified, the ADDDATE function will assume that the left-most portion of the interval value was not provided.
- The ADDDATE function (first syntax only) is a synonym for the DATE_ADD function.
- Using the ADDDATE function with a negative value as a parameter is equivalent to using the SUBDATE function.
- See also the DATE_ADD, DATE_SUB, SUBDATE, ADDTIME, and SUBTIME functions.
Applies To
The ADDDATE function can be used in the following versions of MySQL:
- MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23
Example
Let's look at some MySQL ADDDATE function examples and explore how to use the ADDDATE function in MySQL.
For example:
mysql> SELECT ADDDATE('2014-02-13 08:44:21.000001', INTERVAL 4 MICROSECOND); Result: '2014-02-13 08:44:21.000005' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL -20 SECOND); Result: '2014-02-13 08:44:01' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL 25 MINUTE); Result: '2014-02-13 09:09:21' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL -2 HOUR); Result: '2014-02-13 06:44:21' mysql> SELECT ADDDATE('2014-02-13', INTERVAL 10 DAY); Result: '2014-02-23' mysql> SELECT ADDDATE('2014-02-13', 10); Result: '2014-02-23' mysql> SELECT ADDDATE('2014-02-13', INTERVAL 12 WEEK); Result: '2014-05-08' mysql> SELECT ADDDATE('2014-02-13', INTERVAL -3 MONTH); Result: '2013-11-13' mysql> SELECT ADDDATE('2014-02-13', INTERVAL 3 QUARTER); Result: '2014-11-13' mysql> SELECT ADDDATE('2014-02-13', INTERVAL 5 YEAR); Result: '2019-02-13' mysql> SELECT ADDDATE('2014-02-13 08:44:21.000001', INTERVAL '12.000001' SECOND_MICROSECOND); Result: '2014-02-13 08:44:33.000002' mysql> SELECT ADDDATE('2014-02-13 08:44:21.000001', INTERVAL '3:12.000001' MINUTE_MICROSECOND); Result: '2014-02-13 08:47:33.000002' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL '3:12' MINUTE_SECOND); Result: '2014-02-13 08:47:33' mysql> SELECT ADDDATE('2014-02-13 08:44:21.000001', INTERVAL '1:03:12.000001' HOUR_MICROSECOND); Result: '2014-02-13 09:47:33.000002' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL '1:03:12' HOUR_SECOND); Result: '2014-02-13 09:47:33' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL '1:03' HOUR_MINUTE); Result: '2014-02-13 09:47:21' mysql> SELECT ADDDATE('2014-02-13 08:44:21.000001', INTERVAL '7 1:03:12.000001' DAY_MICROSECOND); Result: '2014-02-20 09:47:33.000002' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL '7 1:03:12' DAY_SECOND); Result: '2014-02-20 09:47:33' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL '7 1:03' DAY_MINUTE); Result: '2014-02-20 09:47:21' mysql> SELECT ADDDATE('2014-02-13 08:44:21', INTERVAL '7 1' DAY_HOUR); Result: '2014-02-20 09:44:21' mysql> SELECT ADDDATE('2014-02-13', INTERVAL '5-3' YEAR_MONTH); Result: '2019-05-13'
Advertisements