ADDDATE() function in MySQL

Last Updated : 13 Sep, 2024

The ADDDATE() function in MySQL is a powerful tool for adding specific time intervals to date or datetime values. It simplifies data manipulation by allowing us to easily calculate future or adjusted dates based on a given starting point. In this article, we will learn about the ADDDATE() function in MySQL by understanding various examples.

ADDDATE() Function in MySQL

The ADDDATE() function in MySQL adds a specified time interval to a date or datetime value.
This function can be used in two different formats:

Syntax:

ADDDATE(date, INTERVAL expr unit)

Parameter:

date: The starting date or datetime value.
expr: The number of time units to add.
unit: The unit of time (e.g., DAY, MONTH, YEAR)adds

Returns:

It returns the date or DateTime after adding the interval.

Examples of ADDDATE() Function in MySQL

Example 1:

Let’s 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:

Let’s 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:

Let’s 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:

Let’s 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:

Let’s 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:

Let’s 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

Explanation: This query selects all columns from the `ScheduleDetails` table and uses the `ADDDATE()` function to add a time interval (`0 days, 5 hours, 5 minutes, 20 seconds`) to the `ScheduledlArrivalTime` column. The result is displayed in a new column named `ExpectedArrivalTime`, which shows the adjusted arrival time of the train.

Conclusion

The ADDDATE() function in MySQL provides a versatile solution for handling date and time manipulations. With support for various time units and precise control over date adjustments, it makes tasks like scheduling and tracking time changes much easier. Whether you’re working with simple day additions or complex interval calculations, ADDDATE() is an essential tool for database developers and users.

DATE_ADD() Function in MySQL

jana_sayantan

Improve

Article Tags :

ADDDATE() function in MySQL