Mysql Date_add Function
MySQL: DATE_ADD Function
This tutorial explains how to use the MySQL DATE_ADD function with syntax and examples.
Description
The MySQL DATE_ADD function returns a date after which a certain time/date interval has been added.
Syntax
The syntax for the DATE_ADD function in MySQL is:
DATE_ADD( date, INTERVAL value unit )Parameters or Arguments
- date
- value
- unit
The date to which the interval should be added.
The value of the time/date interval that you wish to add. You can specify positive and negative values for this parameter.
The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, and so on. It can be one of the following:
| 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 DATE_ADD function will assume that the left-most portion of the interval value was not provided.
- Using the DATE_ADD function with a negative value as a parameter is equivalent to using the DATE_SUB function.
- See also the DATE_SUB, ADDDATE, SUBDATE, ADDTIME, and SUBTIME functions.
Applies To
The DATE_ADD 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 DATE_ADD function examples and explore how to use the DATE_ADD function in MySQL.
For example:
mysql> SELECT DATE_ADD('2014-02-13 08:44:21.000001', INTERVAL 4 MICROSECOND);
Output: '2014-02-13 08:44:21.000005'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL -20 SECOND);
Output: '2014-02-13 08:44:01'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL 25 MINUTE);
Output: '2014-02-13 09:09:21'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL -2 HOUR);
Output: '2014-02-13 06:44:21'
mysql> SELECT DATE_ADD('2014-02-13', INTERVAL 10 DAY);
Output: '2014-02-23'
mysql> SELECT DATE_ADD('2014-02-13', INTERVAL 12 WEEK);
Output: '2014-05-08'
mysql> SELECT DATE_ADD('2014-02-13', INTERVAL -3 MONTH);
Output: '2013-11-13'
mysql> SELECT DATE_ADD('2014-02-13', INTERVAL 3 QUARTER);
Output: '2014-11-13'
mysql> SELECT DATE_ADD('2014-02-13', INTERVAL 5 YEAR);
Output: '2019-02-13'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21.000001', INTERVAL '12.000001' SECOND_MICROSECOND);
Output: '2014-02-13 08:44:33.000002'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21.000001', INTERVAL '3:12.000001' MINUTE_MICROSECOND);
Output: '2014-02-13 08:47:33.000002'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL '3:12' MINUTE_SECOND);
Output: '2014-02-13 08:47:33'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21.000001', INTERVAL '1:03:12.000001' HOUR_MICROSECOND);
Output: '2014-02-13 09:47:33.000002'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL '1:03:12' HOUR_SECOND);
Output: '2014-02-13 09:47:33'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL '1:03' HOUR_MINUTE);
Output: '2014-02-13 09:47:21'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21.000001', INTERVAL '7 1:03:12.000001' DAY_MICROSECOND);
Output: '2014-02-20 09:47:33.000002'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL '7 1:03:12' DAY_SECOND);
Output: '2014-02-20 09:47:33'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL '7 1:03' DAY_MINUTE);
Output: '2014-02-20 09:47:21'
mysql> SELECT DATE_ADD('2014-02-13 08:44:21', INTERVAL '7 1' DAY_HOUR);
Output: '2014-02-20 09:44:21'
mysql> SELECT DATE_ADD('2014-02-13', INTERVAL '5-3' YEAR_MONTH);
Output: '2019-05-13'