MySQL DATE_ADD Function

MySQL DATE_ADD function is beneficial to Perform Arithmetic Operations on date values. Or, say, this MySQL DATE ADD function adds the user-specified intervals to a given date and returns the date or DateTime. In this article, we show how to use this 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 in MySQL 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 range from Microsecond to Year

MySQL DATE_ADD function Examples

The following are the list examples for the Date Add function in MySQL. The below shown queries help you understand the use of this MySQL DATE_ADD function.

MySQL DATE_ADD Function Example 1

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 to refer to the Date Function 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 MySQL DATE_ADD function 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);
MySQL DATE_ADD Function 2

MySQL DATE_ADD Function Example 3

In this MySQL 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 Function 3

DATE_ADD Function Example 4

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);
MySQL DATE_ADD Function 4

MySQL DATE ADD Function Example 5

In this 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);
MySQL DATE ADD Function 5

MySQL DATE_ADD Function Example 6

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);
MySQL DATE ADD Function 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