MySQL ADDDATE() Function | Syntax | Examples

MySQL ADDDATE() Function | Syntax | Examples

In MySQL tutorial, we will discuss about mysql ADDDATE() function. We would love to share with you mysql ADDDATE() functions syntax & examples.

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

MySQL ADDDATE() function

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

Syntax:

The syntax of the ADDDATE function in MySQL is:

ADDDATE( date, INTERVAL value unit )
OR
ADDDATE( date, days )

Params or Arguments

  • date :- The date to which the interval should be added.
  • days:- The number of days to add to date (second syntax).
  • 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 15 days with the date specified in the DATE_ADD() statement given below.

SELECT ADDDATE('2010-05-5', INTERVAL 15 DAY) as date;

Output-1

 +---------------+
 | date          |
 +---------------+
 | 2010-05-20    | 
 +---------------+

Example-2

In the example-2, A date will be returned after adding the 8 months with the date specified in the ADDDATE () statement given below.

SELECT ADDDATE('2010-05-20', INTERVAL 8 MONTH) as date;

Output-2

 +---------------+
 | date          |
 +---------------+
 | 2011-01-20   | 
 +---------------+

Example-3

In the example-2, A date will be returned after adding the 5 years with the date specified in the ADDDATE () statement given below.

SELECT ADDDATE('2010-06-10', INTERVAL 5 YEAR) as date;

Output-3

 +---------------+
 | date          |
 +---------------+
 | 2015-06-10    | 
 +---------------+

Example-4

In the example-2, A date will be returned after adding the 10 days with the date specified in the ADDDATE () statement given below.

SELECT ADDDATE('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 ADDDATE () statement given below.

SELECT ADDDATE('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 ADDDATE () statement given below.

SELECT ADDDATE('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 ADDDATE () statement given below.

SELECT ADDDATE('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 ADDDATE () statement given below.

SELECT ADDDATE('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 ADDDATE () statement given below.

SELECT ADDDATE('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 ADDDATE () statement given below.

SELECT ADDDATE('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 ADDDATE() 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 ADDDATE(‘2018-05-18’, INTERVAL 3 QUARTER);
Output: ‘2018-11-18’

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

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

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

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

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

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

mysql> SELECT ADDDATE(‘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 ADDDATE(‘2018-05-18 06:11:50’, INTERVAL ‘7 1’ DAY_HOUR);
Output: ‘2018-05-25 07:11:50’

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

Conclusion

In this mysql tutorial we have discussed about mysql ADDDATE() 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 *