# DATE\_ADD

## Syntax

```sql
DATE_ADD(date,INTERVAL expr unit)
```

## Description

Performs date arithmetic. The *date* argument specifies the starting date or datetime value. *expr* is an expression specifying the interval value to be added to the starting date. *expr* is a string; it may start with a "`-`" for negative intervals. For composite interval units (such as DAY\_HOUR, MINUTE\_SECOND, and SECOND\_MICROSECOND), *expr* can contain multiple components separated by spaces or punctuation. For example:

* '1 10' DAY\_HOUR represents 1 day and 10 hours
* '1:1' MINUTE\_SECOND represents 1 minute and 1 second
* '1.999999' SECOND\_MICROSECOND represents seconds and microseconds

The exact format of *expr* depends on the *unit* specified.

*unit* is a keyword indicating the units in which the expression should be interpreted. See [Date and Time Units](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date-and-time-units) for a complete list of permitted units.

The result type of `DATE_ADD()` is determined as follows:

* if the first argument is of the type `DATETIME`, the function returns `DATETIME` ;
* if the first argument is `DATE` and the interval uses `HOUR` or smaller units, the function returns `DATETIME` ;
* if the first argument is `DATE` and the interval uses `DAY` or larger units, the function returns `DATE` ;
* similarly, if the first argument is `TIME` and the interval uses `DAY` or smaller units the function returns `TIME`, if the interval uses anything larger, the function returns `DATETIME` ;
* if the first argument isn't a temporal type, the function returns a string.

## Examples

```sql
SELECT '2008-12-31 23:59:59' + INTERVAL 1 SECOND;
+-------------------------------------------+
| '2008-12-31 23:59:59' + INTERVAL 1 SECOND |
+-------------------------------------------+
| 2009-01-01 00:00:00                       |
+-------------------------------------------+
```

```sql
SELECT INTERVAL 1 DAY + '2008-12-31';
+-------------------------------+
| INTERVAL 1 DAY + '2008-12-31' |
+-------------------------------+
| 2009-01-01                    |
+-------------------------------+
```

```sql
SELECT '2005-01-01' - INTERVAL 1 SECOND;
+----------------------------------+
| '2005-01-01' - INTERVAL 1 SECOND |
+----------------------------------+
| 2004-12-31 23:59:59              |
+----------------------------------+
```

```sql
SELECT DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND);
+----------------------------------------------------+
| DATE_ADD('2000-12-31 23:59:59', INTERVAL 1 SECOND) |
+----------------------------------------------------+
| 2001-01-01 00:00:00                                |
+----------------------------------------------------+
```

```sql
SELECT DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY);
+-------------------------------------------------+
| DATE_ADD('2010-12-31 23:59:59', INTERVAL 1 DAY) |
+-------------------------------------------------+
| 2011-01-01 23:59:59                             |
+-------------------------------------------------+
```

```sql
SELECT DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND);
+---------------------------------------------------------------+
| DATE_ADD('2100-12-31 23:59:59', INTERVAL '1:1' MINUTE_SECOND) |
+---------------------------------------------------------------+
| 2101-01-01 00:01:00                                           |
+---------------------------------------------------------------+
```

```sql
SELECT DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR);
+------------------------------------------------------------+
| DATE_ADD('1900-01-01 00:00:00', INTERVAL '-1 10' DAY_HOUR) |
+------------------------------------------------------------+
| 1899-12-30 14:00:00                                        |
+------------------------------------------------------------+
```

```sql
SELECT DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND);
+--------------------------------------------------------------------------------+
| DATE_ADD('1992-12-31 23:59:59.000002', INTERVAL '1.999999' SECOND_MICROSECOND) |
+--------------------------------------------------------------------------------+
| 1993-01-01 00:00:01.000001                                                     |
+--------------------------------------------------------------------------------+
```

## See Also

* [DATE\_SUB](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/date_sub)
* [ADD\_MONTHS](https://mariadb.com/docs/server/reference/sql-functions/date-time-functions/add_months)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}