MySQL DATE_ADD Function

MySQL DATE_ADD function is beneficial to Perform Arithmetic Operations on date values. Or, say, it adds the user-specified intervals to a given date and returns the date or DateTime.

In this article, we show how to use this MySQL DATE_ADD function to add specified intervals to the given date expression with an example.

MySQL DATE_ADD Syntax

The basic syntax of the DATE_ADD Function is

DATE_ADD(Date, INTERVAL expression Unit);
  • Date: It accepts either DateTime value or date in string format.
  • Expression: You have to specify the Value or number of Units to add. For example, 2 DAY means to add two days. This argument accepts negative values for negative intervals.
  • Unit: It accepts unit values ranging from Microsecond to Year

MySQL DATE_ADD function Examples

The following is a list of examples. The below-shown queries help you understand the use of this DATE_ADD function.

DATE_ADD Example

Here, we are adding 1000 Microseconds, 30 Seconds, and 12 Minutes to the given Datetime (2018-12-31 23:30:15.000010).

TIP: I suggest you refer to the Date method article to understand the Units after the MySQL Interval.

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL 1000 MICROSECOND);

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL 30 SECOND);

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL 12 MINUTE);
MySQL DATE_ADD Function 1

MySQL DATE_ADD Function Example 2

In this example, First, we are adding 12 Hours. Next, we added 30 days to a given Datetime expression. Within the third statement, we added 12 Weeks.

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL 12 HOUR);

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL 30 DAY);

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL 12 WEEK);
Add Hours, Days, and Weeks to Date 2

MySQL DATE_ADD Example 3

In this DATE_ADD function example, we added 12 Months, 3 Quarters, and 6 Years to a given Datetime expression.

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL 12 MONTH);

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL 3 QUARTER);

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL 6 YEAR);
MySQL DATE_ADD Example 3

Here, First, we added 33 Seconds and 10000 Microseconds. The second statement adds 15 Minutes, 30 Seconds, and 10005 Microseconds. The third statement adds 12 Minutes and 34 Seconds to a given DateTime.

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL '33.10000' SECOND_MICROSECOND);

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL '15.30.10005' MINUTE_MICROSECOND);

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL '12.34' MINUTE_SECOND);
Add Microseconds to Date 4

MySQL DATE_ADD Example 5

In this MySQL DATE_ADD function example, First, we added 10 Hours, 11 Minutes, 19 Seconds, and 11005 Microseconds. The second statement adds 20 Hours, 10 Minutes, and 11 Seconds. The third statement adds 22 Hours and 09 Minutes to a given DateTime.

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL '10:11.19.11005' HOUR_MICROSECOND);

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL '20:10.11' HOUR_SECOND);

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL '22:09' HOUR_MINUTE);
Example 5

We added 31 Days, 10 Hours, 11 Minutes, 19 Seconds, and 11005 Microseconds. The second statement adds 31 Days, 10 Hours, 11 Minutes, and 19 Seconds. The third statement adds 60 Days, 20 Hours, and 19 Minutes to a given DateTime.

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL ' 31 10:11.19.11005' DAY_MICROSECOND);

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL ' 31 10:11.19' DAY_SECOND);

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL ' 60 20:19' DAY_MINUTE);
Example 6

Here, we added 06 Years to the given DateTime value.

SELECT DATE_ADD('2018-12-31 23:30:15.000010', INTERVAL '06-12' YEAR_MONTH);

SELECT DATE_ADD('2018-12-31 23:30:15', INTERVAL '06-12' YEAR_MONTH);
MySQL DATE_ADD Function 7