How to Use the DATE_ADD() Function in MySQL

08/05/2021

Contents

In this article, you will learn how to use the DATE_ADD() function in MySQL.

Using the DATE_ADD() function in MySQL

The DATE_ADD() function in MySQL is used to add a specified interval to a date. It can be used to add seconds, minutes, hours, days, weeks, months, or years to a date. This function is useful when you need to calculate a future date or time based on a specific interval.

Syntax

DATE_ADD(date, INTERVAL value unit);

Examples

Here are some examples of using the DATE_ADD() function in MySQL:

Adding days to a date

To add a specified number of days to a date, you can use the DATE_ADD() function with the DAY interval.

Syntax
SELECT DATE_ADD(date, INTERVAL number_of_days DAY);
Example
SELECT DATE_ADD('2022-05-15', INTERVAL 7 DAY);

Output:

+-----------------------------------+
| DATE_ADD('2022-05-15', INTERVAL 7 DAY) |
+-----------------------------------+
| 2022-05-22                        |
+-----------------------------------+

In this example, the DATE_ADD() function adds 7 days to the date ‘2022-05-15’ to return a future date of ‘2022-05-22’.

Adding months to a date

To add a specified number of months to a date, you can use the DATE_ADD() function with the MONTH interval.

Syntax
SELECT DATE_ADD(date, INTERVAL number_of_months MONTH);
Example
SELECT DATE_ADD('2022-05-15', INTERVAL 3 MONTH);

Output:

+------------------------------------+
| DATE_ADD('2022-05-15', INTERVAL 3 MONTH) |
+------------------------------------+
| 2022-08-15                         |
+------------------------------------+

In this example, the DATE_ADD() function adds 3 months to the date ‘2022-05-15’ to return a future date of ‘2022-08-15’.

Adding years to a date

To add a specified number of years to a date, you can use the DATE_ADD() function with the YEAR interval.

Syntax
SELECT DATE_ADD(date, INTERVAL number_of_years YEAR);
Example
SELECT DATE_ADD('2022-05-15', INTERVAL 2 YEAR);

Output:

+----------------------------------+
| DATE_ADD('2022-05-15', INTERVAL 2 YEAR) |
+----------------------------------+
| 2024-05-15                       |
+----------------------------------+

In this example, the DATE_ADD() function adds 2 years to the date ‘2022-05-15’ to return a future date of ‘2024-05-15’.

Adding time intervals to a dateTime value

You can also use the DATE_ADD() function to add time intervals to a datetime value.

Syntax
SELECT DATE_ADD(datetime_value, INTERVAL value unit);
Example
SELECT DATE_ADD('2022-05-15 08:30:00', INTERVAL 2 HOUR);

Output:

+--------------------------------------------------+
| DATE_ADD('2022-05-15 08:30:00', INTERVAL 2 HOUR) |
+--------------------------------------------------+
| 2022-05-15 10:30:00                               |
+--------------------------------------------------+

In this example, the DATE_ADD() function adds 2 hours to the datetime value ‘2022-05-15 08:30:00’ to return a new datetime value of ‘2022-05-15 10:30:00’.

#MySQL