MySQL ADDDATE() function; Thorugh this tutorial, i am going to show you MySQL ADDDATE() function with the help of examples.
MySQL ADDDATE() function
ADDDATE() function in MySQL is used to add the specified intervals to the given date and time. It returns the date or DateTime after adding the interval. Parameter : This method accepts two parameter. date: The given date which we want to modify
Syntax of MySQL ADDDATE() function
The syntax of the ADDDATE function in MySQL is:
ADDDATE( date, INTERVAL value unit )
OR
ADDDATE( date, days )
Parameters of MySQL ADDDATE() function
- 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 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’ |
Example 1 – MySQL ADDDATE() function
To add 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 – MySQL ADDDATE() function
To add 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 – MySQL ADDDATE() function
To add 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 – MySQL ADDDATE() function
To add 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 – MySQL ADDDATE() function
To minus 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 – MySQL ADDDATE() function
To minus 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 – MySQL ADDDATE() function
To minus 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 – MySQL ADDDATE() function
To add 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 – MySQL ADDDATE() function
To add 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 – MySQL ADDDATE() function
To add 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 | +-----------------------+
Conclusion
In this mysql tutorial we have discussed about mysql ADDDATE() function with various examples.