DATE_ADD MySQL Function with Examples

MySQL: DATE_ADD function; In this tutorial, we would love to share with you how to use MySQL DATE_ADD function with the help of 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 Value	Expected expression Format
MICROSECOND	MICROSECONDS
SECOND	SECONDS
MINUTE	MINUTES
HOUR	HOURS
DAY	DAYS
WEEK	WEEKS
MONTH	MONTHS
QUARTER	QUARTERS
YEAR	YEARS
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   | 
 +-----------------------+

Conclusion

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

Recommended MySQL Tutorials

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