SQL Tutorial SQL Advanced SQL Database SQL References

MySQL ADDDATE() Function



The MySQL ADDDATE() function adds a specified time/date interval to a date/datetime value and then returns the modified date/datetime value.

This function (version 1 syntax) is a synonym for the DATE_ADD() function.

Using this function with a negative value as a parameter is equivalent to using the SUBDATE() function.

Syntax

/* version 1 */
ADDDATE(datetime, INTERVAL value unit)

/* version 2 */
ADDDATE(datetime, days)

Parameters

datetime Required. Specify a date or datetime value to be modified.
days Required. Specify the number of days to add to datetime. Both positive and negative values are allowed.
value Required. Specify the value of the time/date interval to add. Both positive and negative values are allowed.
unit Required. Specify the unit type of interval to add. It can be one of the following values:
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

Return Value

Returns the modified date/datetime value.

Example 1:

The example below shows the usage of ADDDATE() function.

mysql> SELECT ADDDATE('2019-10-25', 10);
Result: '2019-11-04'

mysql> SELECT ADDDATE('2019-10-25', INTERVAL 10 DAY);
Result: '2019-11-04'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL 2 HOUR);
Result: '2019-10-25 10:10:25'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL -2 HOUR);
Result: '2019-10-25 06:10:25'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL 30 MINUTE);
Result: '2019-10-25 08:40:25'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL -10 SECOND);
Result: '2019-10-25 08:10:15'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL 1 WEEK);
Result: '2019-11-01 08:10:25'

mysql> SELECT ADDDATE('2019-10-25', INTERVAL -2 MONTH);
Result: '2019-08-25'

mysql> SELECT ADDDATE('2019-10-25', INTERVAL 2 QUARTER);
Result: '2020-04-25'

mysql> SELECT ADDDATE('2019-10-25', INTERVAL 2 YEAR);
Result: '2021-10-25'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL 10 MICROSECOND);
Result: '2019-10-25 08:10:25.000010'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '5.0000010' SECOND_MICROSECOND);
Result: '2019-10-25 08:10:30.000010'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '10:5.0000010' MINUTE_MICROSECOND);
Result: '2019-10-25 08:20:30.000010'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '10:5' MINUTE_SECOND);
Result: '2019-10-25 08:20:30'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '2:10:5.0000010' HOUR_MICROSECOND);
Result: '2019-10-25 10:20:30.000010'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '2:10:5' HOUR_SECOND);
Result: '2019-10-25 10:20:30'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '2:10' HOUR_MINUTE);
Result: '2019-10-25 10:20:25'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '3 2:10:5.0000010' DAY_MICROSECOND);
Result: '2019-10-28 10:20:30.000010'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '3 2:10:5' DAY_SECOND);
Result: '2019-10-28 10:20:30'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '3 2:10' DAY_MINUTE);
Result: '2019-10-28 10:20:25'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '3 2' DAY_HOUR);
Result: '2019-10-28 10:10:25'

mysql> SELECT ADDDATE('2019-10-25 08:10:25', INTERVAL '2 5' YEAR_MONTH);
Result: '2022-03-25 08:10:25'

Example 2:

Consider a database table called EmployeeLogin with the following records:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:50:38
2Marry2019-10-25 09:21:052019-10-25 17:51:05
3Jo2019-10-25 09:24:352019-10-25 17:54:35
4Kim2019-10-25 09:25:242019-10-25 17:55:24
5Ramesh2019-10-25 09:27:162019-10-25 17:57:16

To insert a new record in this table, the following statement can be used.

INSERT INTO EmployeeLogin 
VALUES (6, 'Suresh', NOW(), ADDDATE(NOW(), INTERVAL '8:30' HOUR_MINUTE));

-- see the result
SELECT * FROM EmployeeLogin;

This will produce a result similar to:

EmpIDNameLogin StampExpected Logout Stamp
1John2019-10-25 09:20:382019-10-25 17:50:38
2Marry2019-10-25 09:21:052019-10-25 17:51:05
3Jo2019-10-25 09:24:352019-10-25 17:54:35
4Kim2019-10-25 09:25:242019-10-25 17:55:24
5Ramesh2019-10-25 09:27:162019-10-25 17:57:16
6Suresh2019-10-25 09:28:192019-10-25 17:58:19

❮ MySQL Functions