MySQL DATE_ADD Function

MySQL DATE_ADD function; In this tutorial, i am going to show you MySQL DATE_ADD function with the help of examples.

MySQL DATE_ADD() function

The DATE_ADD() function adds a time/date interval to a date and then returns the date.

Syntax of MySQL DATE_ADD() function

The syntax of the DATE_ADD function in MySQL is:

 DATE_ADD( date, INTERVAL value unit )

Parameters of MySQL DATE_ADD() function

  • date :- The date to which the interval should be added.
  • value:- The value of the time/date interval that you wish to add. You can specify positive and negative values for this parameter (first syntax).
  • unit:- The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, and so on.

The following table shows the expected form of the expression or argument for each unit value.

Unit ValueExpected expression Format
MICROSECONDMICROSECONDS
SECONDSECONDS
MINUTEMINUTES
HOURHOURS
DAYDAYS
WEEKWEEKS
MONTHMONTHS
QUARTERQUARTERS
YEARYEARS
SECOND_MICROSECOND‘SECONDS.MICROSECONDS’
MINUTE_MICROSECOND‘MINUTES:SECONDS.MICROSECONDS’
MINUTE_SECOND‘MINUTES:SECONDS’
HOUR_MICROSECOND‘HOURS:MINUTES:SECONDS.MICROSECONDS’
HOUR_SECOND‘HOURS:MINUTES:SECONDS’
HOUR_MINUTE‘HOURS:MINUTES’
DAY_MICROSECOND‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
DAY_SECOND‘DAYS HOURS:MINUTES:SECONDS’
DAY_MINUTE‘DAYS HOURS:MINUTES’
DAY_HOUR‘DAYS HOURS’
YEAR_MONTH‘YEARS-MONTHS’

Example 1 – MySQL DATE_ADD() function

To add the 20 days with the date specified in the DATE_ADD() statement given below.

SELECT  DATE_ADD('2010-05-5', INTERVAL 20 DAY) as date;

Output-1

 +---------------+
 | date          |
 +---------------+
 | 2010-05-25    | 
 +---------------+

Example 2 – MySQL DATE_ADD() function

To add the 11 months with the date specified in the ADDDATE () statement given below.

SELECT DATE_ADD('2010-05-20', INTERVAL 11 MONTH) as date;

Output-2

 +---------------+
 | date          |
 +---------------+
 | 2011-04-20    | 
 +---------------+

Example 3 – MySQL DATE_ADD() function

To add the 6 years with the date specified in the DATE_ADD() statement given below.

SELECT  DATE_ADD('2010-06-10', INTERVAL 6 YEAR) as date;

Output-3

 +---------------+
 | date          |
 +---------------+
 | 2016-06-10    | 
 +---------------+

Example 4 – MySQL DATE_ADD() function

To add the 10 days with the date specified in the DATE_ADD() statement given below.

SELECT DATE_ADD('2018-6-15',10 );

Output-4

 +---------------+
 | date          |
 +---------------+
 | 2018-06-25   | 
 +---------------+

Example 5 – MySQL DATE_ADD() function

To minus 40 second with the date specified in the DATE_ADD() statement given below.

SELECT DATE_ADD('2018-05-13 09:55:21', INTERVAL -40 SECOND);

Output-5

  +-----------------------+
  | date                  |
  +-----------------------+
  | 2018-05-13 09:54:41   | 
  +-----------------------+

Example 6 – MySQL DATE_ADD() function

To minus the 3 hour with the date specified in the DATE_ADD() statement given below.

SELECT DATE_ADD('2018-05-13 09:55:21', INTERVAL -3 HOUR);

Output-6

 +-----------------------+
 | date                  |
 +-----------------------+
 | 2018-05-13 06:55:21   | 
 +-----------------------+

Example 7 – MySQL DATE_ADD() function

To minus the 3 month with the date specified in the DATE_ADD() statement given below.

SELECT  DATE_ADD('2018-05-13 09:55:21', INTERVAL -3 MONTH);

Output-7

 +-----------------------+
 | date                  |
 +-----------------------+
 | 2018-02-13 09:55:21   | 
 +-----------------------+

Example 8 – MySQL DATE_ADD() function

To add 12 weeks with the date specified in the DATE_ADD() statement given below.

SELECT  DATE_ADD('2018-02-13 09:55:21', INTERVAL 12 WEEK);

Output-8

 +-----------------------+
 | date                  |
 +-----------------------+
 | 2018-05-08 09:55:21   | 
 +-----------------------+

Example 9 – MySQL DATE_ADD() function

To add 7 days & seconds with the date specified in the DATE_ADD() statement given below.

SELECT  DATE_ADD('2018-04-08 09:55:21', INTERVAL '7 1:03:12' DAY_SECOND);

Output-9

 +-----------------------+
 | date                  |
 +-----------------------+
 | 2018-04-15 10:58:33   | 
 +-----------------------+

Example 10 – MySQL DATE_ADD() function

To add 7 days & minutes with the date specified in the DATE_ADD() statement given below.

SELECT  DATE_ADD('2018-02-16 06:47:11', INTERVAL '7 1:03' DAY_MINUTE);

Output-10

 +-----------------------+
 | date                  |
 +-----------------------+
 | 2018-02-23 07:50:11   | 
 +-----------------------+

Conclusion

In this MySQL tutorial, we have discussed about mysql DATE_ADD() function with various examples.

Leave a Comment