MariaDB ADDDATE() Function

In MariaDB, the ADDDATE() function adds the specified time interval to the specified date/time and returns the operation result.

MariaDB ADDDATE() Syntax

This is the syntax of the MariaDB ADDDATE() function:

ADDDATE(date, days)
ADDDATE(date, INTERVAL value unit)

Parameters

date

Required. The date value.

days

Required. The number of days to add to date.

value

Required. The time/date interval. Both positive and negative numbers are allowed.

unit

Required. The unit of the time/date interval.

The unit of the time/date interval 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

If you provide no parameters or the wrong number of parameters, MariaDB will report an error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1.

Return value

The MariaDB ADDDATE() function adds the specified time interval to the specified date/time and returns a new date/time. The returned value of the ADDDATE() function is related to the parameters:

  • If the date argument is of DATE type and the interval is YEAR, MONTH or DAY, Returns DATE.
  • If the date argument is of DATE type and the interval is HOURS, MINUTES or SECONDS, returns DATETIME.
  • If the date parameter is of DATETIME type, returns DATETIME.
  • If the date argument is of TIME type and the interval is YEAR, MONTH or DAY, returns DATETIME.
  • Otherwise returns a string.

MariaDB ADDDATE() Examples

Example 1 - Add days

To add days to a given date, use the following statement with MariaDB ADDDATE() function:

SELECT ADDDATE('2023-01-05', 5);

Output:

+--------------------------+
| ADDDATE('2023-01-05', 5) |
+--------------------------+
| 2023-01-10               |
+--------------------------+

MariaDB ADDDATE() allows you to add days to a datetime value:

SELECT ADDDATE('2023-01-05 10:11:12', 5);

Output:

+-----------------------------------+
| ADDDATE('2023-01-05 10:11:12', 5) |
+-----------------------------------+
| 2023-01-10 10:11:12               |
+-----------------------------------+

Example 2 - INTERVAL

MariaDB ADDDATE() allows you to provide a value of INTERVAL type:

SELECT
  ADDDATE('2023-01-05', INTERVAL 10 DAY),
  ADDDATE('2023-01-05', INTERVAL 10 HOUR),
  ADDDATE('2023-01-05 10:11:12', INTERVAL 10 HOUR),
  ADDDATE('2023-01-05 10:11:12', INTERVAL 10 MINUTE)\G

Output:

            ADDDATE('2023-01-05', INTERVAL 10 DAY): 2023-01-15
           ADDDATE('2023-01-05', INTERVAL 10 HOUR): 2023-01-05 10:00:00
  ADDDATE('2023-01-05 10:11:12', INTERVAL 10 HOUR): 2023-01-05 20:11:12
ADDDATE('2023-01-05 10:11:12', INTERVAL 10 MINUTE): 2023-01-05 10:21:12

Example 3 - Subtract days

MariaDB ADDDATE() allows to subtract a given interval by providing a negative value:

example:

SELECT
  ADDDATE('2023-01-05', -5),
  ADDDATE('2023-01-05', INTERVAL -5 DAY)\G

Output:

             ADDDATE('2023-01-05', -5): 2022-12-31
ADDDATE('2023-01-05', INTERVAL -5 DAY): 2022-12-31

Conclusion

In MariaDB, the ADDDATE() function adds the specified time interval to the specified date/time and returns the operation result.