Home » MySQL » MySQL date/time Functions

MySQL ADDDATE() Function

MySQL | ADDDATE() Function: Learn about the ADDDATE() function, how it works, its usages, syntax, and examples.
Submitted by Apurva Mathur, on September 27, 2022

ADDDATE() Function

Suppose you are handling a large database containing 1000 entries, and this table contains a column named the date of birth which contains different dates and times, and for some reason, you are being asked to apply some query that can give you the exact date after 6 years from the given date of birth, now what will you do in such situation? For such a situation MYSQL provides us the function named ADDDATE() function, adds an interval to some date and as a result, it will return you the date in the particular interval.

There are different types of intervals that we can add to this function, which are as follows,

• MICROSECOND
• SECOND
• MINUTE
• 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

ADDDATE() Syntax

SELECT ADDDATE(DATE, INTERVAL VALUE UNIT );

ADDDATE() Parameter(s)

• DATE: Date in the format 'yyyy:dd:mm' in which the particular interval will be added.
• INTERVAL VALUE: This is the numeric value that can normally be called duration. Where UNIT is the type of interval you want to apply on your date.

ADDDATE() Return Value

It will return the date after applying the interval value you have provided.

MySQL ADDDATE() Function Example 1

SELECT ADDDATE("2001-04-12", INTERVAL 20 YEAR) as after_20_year;

Output:

As you can see the result returned us the date after the given interval.

MySQL ADDDATE() Function Example 2

SELECT ADDDATE("2006-04-25", INTERVAL 8 MONTH);

Output:

MySQL ADDDATE() Function Example 3

Now suppose I have a table named date_of_birth and inside this table, I have the following columns;

And now if we want to know the time after 12 hours of interval then we can write,

SELECT ADDDATE(date_of_birth, interval 12 hour) FROM date_of_birth;

Output:

---