MySQL — Add Days to a Datetime

MySQL supports dates and date times as column types. It’s a nice way to keep track of when things happen or should happen. Working with dates and times can be hard, but adding days to a date time column value isn’t.

This tutorial shows you how to add days to a MySQL date time value directly in a database query.

MySQL Series Overview

Add Days to a Date in MySQL

MySQL has dozens of integrated date and time functions. Two of them are ADDDATE and DATE_ADD. You can use both of these functions to add days to a date value in a given column. Both MySQL functions, ADDDATE and DATE_ADD, support an INTERVAL expression for the added days. Additionally, ADDDATE also supports an integer value besides the interval to define the added days.

Here’s an overview of the comparison between the two functions:

  • ADDDATE: supports INTERVAL and integer values for added days
  • DATE_ADD: supports only an INTERVAL value

Both functions work in the same way if you’re using an INTERVAL expression as the second argument:

SELECT DATE_ADD('2024-05-16', INTERVAL 7 DAY);  
-- '2024-05-23'

-- '2024-05-23'

You can find all supported temporal interval expressions in the MySQL docs.

When using the ADDDATE function, you can also use a “days” form: in such cases, MySQL treats the second argument as an integer number representing the days to add:

SELECT ADDDATE('2024-05-16', 7);  
-- '2024-05-23'

SELECT ADDDATE('2024-05-16', 35);  
-- '2024-06-20'

Handling Dates in Leap Years

MySQL automatically handles leap years when adding days to a given date. For example, we had a leap year in 2024. Adding one day to February, 28th 2024 results in the 29th of February. In contrast, adding one day to February 28th, 2025 results in March 1st:

SELECT DATE_ADD('2024-02-28', INTERVAL 1 DAY);  
-- '2024-02-29'

SELECT DATE_ADD('2024-02-28', INTERVAL 2 DAY);  
-- '2024-03-01'

SELECT DATE_ADD('2025-02-28', INTERVAL 1 DAY);  
-- '2025-03-01'

Add Days in a SELECT Statement

You can add days in a SELECT statement and use the result as a named column. For example, you may calculate a trial end date within the database using MySQL’s DATE_ADD function. Here’s a sample code adding seven days to the trial starting date to calculate the end date:

    DATE_ADD(trial_startedon, INTERVAL 7 DAY) as trial_endson
FROM users  

Add Days in an UPDATE Statement

You can also add days to a date in MySQL within an UPDATE query. For example, you could set calculate a trial end date based on “now”.

Here’s the sample code setting a trial_endson column value based on the calculation of a date function:

UPDATE users  
SET trial_endson = DATE_ADD(NOW(), INTERVAL 30 DAY);  

That’s it. Enjoy working with dates in MySQL!

Mentioned Resources

Explore the Library

Find interesting tutorials and solutions for your problems.