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
- Truncate a Foreign Key Constrained Table
- Add Days to a Datetime
- Find Value in Column with Comma-Separated Values
- Ideas to Debug High-Load Situations (Coming soon)
- Calculate Number of Days Between Two Dates
- Get the Last Day of the Month
- Get the First Day of the Month
- Select Rows With Date Within a Month
- Get Weekday From Date (Coming soon)
- Fixing Invalid JSON Text in Argument 1 to Function json_extract: "Invalid value." (Coming soon)
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
: supportsINTERVAL
and integer values for added daysDATE_ADD
: supports only anINTERVAL
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'
SELECT ADDDATE('2024-05-16', INTERVAL 7 DAY);
-- '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:
SELECT
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!