DATE_ADD of MySQL Function with Various Examples

DATE_ADD of MySQL Function with Various Examples

This MySQL tutorial, we would love to share with you how to use MySQL DATE_ADD function with it’s syntax and examples.

In our previous tutorial, we have shared a list of MySQL Date functions that are available to work with Date and time.

MySQL DATE_ADD() function

The MySQL DATE_ADD function returns a date after which a certain date, time, hours, seconds, days, weeks, interval has been added.

For example, you can use it to add 10 years to a given date. You can add minutes, hours, seconds, microseconds, days, weeks, months, quarters, years etc.

Syntax:

The syntax of the DATE_ADD function in MySQL is:

 DATE_ADD( date, INTERVAL value unit )

Params or Arguments

  • 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’

Examples

Let’s look at some MySQL ADDDATE function examples and explore how to use the ADDDATE function in MySQL.

Example-1

In the example-1, A date will be returned after adding 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

In the example-2, A date will be returned after adding 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

In the example-2, A date will be returned after adding 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

In the example-2, A date will be returned after adding 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

In the example-5, A date will be returned after decreasing the 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

In the example-6, A date will be returned after decreasing 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

In the example-7, A date will be returned after decreasing 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

In the example-8, A date will be returned after adding the 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

In the example-9, A date will be returned after adding the 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

In the example-10, A date will be returned after adding the 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   | 
 +-----------------------+

More Queries

You can also try MySQL DATE_ADD() functions with SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_SECOND, HOUR_MINUTE, DAY_HOUR, HOUR_MICROSECOND, YEAR_MONTH, DAY_HOUR, DAY_MICROSECOND etc. We will provide a queries below with it’s output.

mysql> SELECT DATE_ADD(‘2018-05-18’, INTERVAL 3 QUARTER);
Output: ‘2018-11-18’

mysql> SELECT DATE_ADD(‘2018-05-18 06:11:50.000001’, INTERVAL ‘12.000001’ SECOND_MICROSECOND);
Output: ‘2018-05-18 06:12:02.000002’

mysql> SELECT DATE_ADD(‘2018-05-18 06:11:50.000001’, INTERVAL ‘3:12.000001’ MINUTE_MICROSECOND);
Output: ‘2018-05-18 06:15:02.000002’

mysql> SELECT DATE_ADD(‘2018-05-18 06:11:50’, INTERVAL ‘3:12’ MINUTE_SECOND);
Output: ‘2018-05-18 06:15:02’

mysql> SELECT DATE_ADD(‘2018-05-18 06:11:50.000001’, INTERVAL ‘1:03:12.000001’ HOUR_MICROSECOND);
Output: ‘2018-05-18 09:47:33.000005’

mysql> SELECT DATE_ADD(‘2018-05-18 06:11:50’, INTERVAL ‘1:03:12’ HOUR_SECOND);
Output: ‘2018-05-18 07:15:02.000002’

mysql> SELECT DATE_ADD(‘2018-05-18 06:11:50’, INTERVAL ‘1:03’ HOUR_MINUTE);
Output: ‘2018-05-18 07:14:50’

mysql> SELECT DATE_ADD(‘2018-05-18 06:11:50.000001’, INTERVAL ‘7 1:03:12.000001’ DAY_MICROSECOND);
Output: ‘2018-05-25 07:15:02.000002’

mysql> SELECT DATE_ADD(‘2018-05-18 06:11:50’, INTERVAL ‘7 1’ DAY_HOUR);
Output: ‘2018-05-25 07:11:50’

mysql> SELECT DATE_ADD(‘2018-05-18’, INTERVAL ‘5-3’ YEAR_MONTH);
Output: ‘2023-08-18’

Conclusion

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

If you have any questions or thoughts to share, use the comment form below to reach us.

Leave a Reply

Your email address will not be published. Required fields are marked *