ADDDATE() function in MySQL

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.

Syntax :

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

Parameter : This method accepts two parameter.

• date: The given date which we want to modify .
• days :The number of days to which we want to add to given date .
• expr : A date or datetime expression or a number. It can be given in following format –
  • MICROSECONDS
  • SECONDS
  • MINUTES
  • HOURS
  • DAYS
  • WEEKS
  • MONTHS
  • QUARTERS
  • YEARS
  • ‘SECONDS.MICROSECONDS’
  • ‘MINUTES:SECONDS.MICROSECONDS’
  • ‘MINUTES:SECONDS
  • ‘HOURS:MINUTES:SECONDS.MICROSECONDS’
  • ‘HOURS:MINUTES:SECONDS’
  • ‘HOURS:MINUTES’
  • ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’
  • ‘DAYS HOURS:MINUTES:SECONDS’
  • ‘DAYS HOURS:MINUTES’
  • ‘DAYS HOURS’
  • ‘YEARS-MONTHS’

• Unit : The type of interval to add. It is one of the given type –
  • MICROSECOND
  • SECOND
  • MINUTES
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH

Returns : It returns the date or DateTime after adding the interval.

Example-1 :
Adding 15 days with the specified date using ADDDATE Function.

SELECT ADDDATE('2020-08-20', INTERVAL 15 DAY) 
as Updated_date;

Output :

Updated_date
2020-09-04

Example-2 :
Adding 30 minutes with the specified datetime using ADDDATE Function.

SELECT ADDDATE('2020-08-28 20:59:59', INTERVAL 30 MINUTE) 
as Updated_datetime;

Output :

Updated_datetime
2020-08-28 21:29:59

Example-3 :
Adding 4 weeks with the specified date using ADDDATE Function.

SELECT ADDDATE('2020-08-12', INTERVAL 4 WEEK) 
as Updated_date;

Output :

Updated_date
2020-09-09

Example-4 :
Adding 6 months with the specified date using ADDDATE Function.

SELECT ADDDATE('2019-08-12', INTERVAL 6 MONTH) 
as Updated_date ;

Output :

Updated_date
2020-02-12

Example-5 :
Adding 10 years with the specified date using ADDDATE Function.

SELECT ADDDATE('2010-12-10', INTERVAL 10 YEAR) 
as Updated_date ;

Output :

Updated_date
2020-12-10

Example-6 :
Adding 5 days 10 hour 05 minute  20 seconds with the specified datetime using ADDDATE Function.

SELECT ADDDATE('2020-08-20 05:15:19', INTERVAL '5-10-05-20' DAY_SECOND) 
as Updated_datetime;

Output :

Updated_datetime
2020-08-25 15:20:39

Example-7 :
The ADDDATE function can be used to set value of columns. To demonstrate create a table named ScheduleDetails.

CREATE TABLE ScheduleDetails(
TrainId INT NOT NULL,
StationName VARCHAR(20) NOT NULL,
TrainName VARCHAR(20) NOT NULL,
ScheduledlArrivalTime DATETIME NOT NULL,
PRIMARY KEY(TrainId )
);

Now inserting values in ScheduleDetails table. We will use ADDDATE function which will denote delay in arrival timing. The value in ExpectedArrivalTime column will be the value given by ADDDATE Function.

INSERT INTO  
ScheduleDetails (TrainId, StationName, TrainName, ScheduledlArrivalTime )
VALUES
(12859, 'KGP', 'Gitanjali Express ', '2020-11-17 10:20:10');

Now, checking the ScheduleDetails table :

SELECT *, ADDDATE(ScheduledlArrivalTime, INTERVAL '0-5-05-20' DAY_SECOND)  
AS ExpectedArrivalTime FROM ScheduleDetails;

Output :

TRAINID	STATIONNAME	TRAINNAME	SCHEDULEDARRIVALTIME	EXPECTEDARRIVALTIME
12859	KGP	Gitanjali Express	2020-11-17 10:20:10	2020-11-17 15:25:30