How to Add Days to a Date in MySQL Using DATE_ADD()
Add Days to a Date in MySQL, Date calculations are everywhere in data analysis. You may need to find a follow-up date, calculate a delivery deadline, create a trial expiration date, or simply determine what date falls a certain number of days after an event.
In MySQL, one of the easiest ways to do this is with the DATE_ADD() function.
For example, to add seven days to a date:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL7DAY) AS seven_days_laterFROM sales;
MySQL calculates the new date for every row without changing the original value stored in the table.
MySQL DATE_ADD() Syntax for Days
The basic syntax is:
DATE_ADD(date, INTERVAL number DAY)
For example:
SELECT DATE_ADD('2024-02-10', INTERVAL7DAY);Result:
2024-02-17
Here:
DATE_ADD()performs the date calculation.'2024-02-10'is the starting date.7is the number of days.DAYspecifies the unit being added.
You can replace 7 with any number required by your analysis.
Example: Adding Days to Sales Dates
Let’s create a simple sales table containing several grocery transactions.
CREATETABLE sales ( store_ID INTPRIMARYKEY, item TEXT NOTNULL, sales_date DATENOTNULL);
Insert some sample data:
INSERTINTO sales VALUES (1, 'Oranges', '2024-02-10');INSERTINTO sales VALUES (2, 'Apples', '2024-11-25');INSERTINTO sales VALUES (3, 'Bananas', '2024-07-30');INSERTINTO sales VALUES (4, 'Melons', '2024-01-14');INSERTINTO sales VALUES (5, 'Grapes', '2024-05-19');
We can check the table with:
SELECT*FROM sales;
The data looks like this:
+----------+---------+------------+| store_ID | item | sales_date |+----------+---------+------------+| 1 | Oranges | 2024-02-10 || 2 | Apples | 2024-11-25 || 3 | Bananas | 2024-07-30 || 4 | Melons | 2024-01-14 || 5 | Grapes | 2024-05-19 |+----------+---------+------------+
Now suppose we want to find the date exactly seven days after each sale.
We can use:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL7DAY)FROM sales;
The result is:
+------------+--------------------------------------+| sales_date | DATE_ADD(sales_date, INTERVAL 7 DAY) |+------------+--------------------------------------+| 2024-02-10 | 2024-02-17 || 2024-11-25 | 2024-12-02 || 2024-07-30 | 2024-08-06 || 2024-01-14 | 2024-01-21 || 2024-05-19 | 2024-05-26 |+------------+--------------------------------------+
MySQL automatically handles the transition between months.
For example:
2024-11-25 + 7 days = 2024-12-02
No manual calculation is necessary.
Give the New Date a Meaningful Name
The default expression generated by MySQL can be difficult to read.
A column alias makes the result much clearer:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL7DAY) AS add_sevenFROM sales;
Output:
+------------+------------+| sales_date | add_seven |+------------+------------+| 2024-02-10 | 2024-02-17 || 2024-11-25 | 2024-12-02 || 2024-07-30 | 2024-08-06 || 2024-01-14 | 2024-01-21 || 2024-05-19 | 2024-05-26 |+------------+------------+
An even more descriptive name could be:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL7DAY) AS seven_days_laterFROM sales;
Using meaningful aliases is particularly helpful when the query becomes part of a report or dashboard.
You Can Add Any Number of Days
DATE_ADD() isn’t limited to seven days.
For example, add one day:
SELECT DATE_ADD(sales_date, INTERVAL1DAY)FROM sales;
Add 14 days:
SELECT DATE_ADD(sales_date, INTERVAL14DAY)FROM sales;
Add 30 days:
SELECT DATE_ADD(sales_date, INTERVAL30DAY)FROM sales;
Add 90 days:
SELECT DATE_ADD(sales_date, INTERVAL90DAY)FROM sales;
The general pattern doesn’t change:
DATE_ADD(date, INTERVAL number DAY)
Adding Days Across Months
One of the useful features of MySQL date functions is that you don’t need to worry about month boundaries.
For example:
SELECT DATE_ADD('2024-01-28', INTERVAL7DAY);returns:
2024-02-04
Similarly:
SELECT DATE_ADD('2024-12-28', INTERVAL7DAY);returns:
2025-01-04
MySQL takes care of the change in month and year automatically.
This makes DATE_ADD() much safer and more convenient than manually manipulating the day, month, and year components.
Adding Days to a Column
You don’t have to use a fixed date.
The date can come directly from a table column:
SELECT item, sales_date, DATE_ADD(sales_date, INTERVAL7DAY) AS follow_up_dateFROM sales;
This produces a useful result such as:
+---------+------------+---------------+| item | sales_date | follow_up_date |+---------+------------+---------------+| Oranges | 2024-02-10 | 2024-02-17 || Apples | 2024-11-25 | 2024-12-02 || Bananas | 2024-07-30 | 2024-08-06 || Melons | 2024-01-14 | 2024-01-21 || Grapes | 2024-05-19 | 2024-05-26 |+---------+------------+---------------+
This is often more useful than simply returning the calculated date because the original date and future date can be compared side by side.
Using a Variable Number of Days
What if every record needs a different number of days?
Suppose your table contains:
item sales_date delivery_daysOranges 2024-02-10 5Apples 2024-11-25 7Bananas 2024-07-30 3
You can use the column containing the number of days directly:
SELECT item, sales_date, DATE_ADD(sales_date, INTERVAL delivery_days DAY) AS expected_deliveryFROM sales;
This is useful for datasets where the time interval varies from one record to another.
A Real-World Example: Delivery Dates
Imagine an online store where every order has an estimated delivery period.
You might have:
order_datedelivery_days
Instead of manually calculating delivery dates, MySQL can do it:
SELECT order_id, order_date, DATE_ADD(order_date, INTERVAL delivery_days DAY) AS delivery_dateFROM orders;
The database calculates the expected delivery date for every order.
This same approach can be used for:
- Shipping deadlines
- Customer follow-ups
- Trial expiration dates
- Appointment reminders
- Warranty periods
- Project deadlines
- Payment due dates
Another Example: Customer Follow-Up
Suppose a sales team wants to contact customers seven days after an initial purchase.
You could calculate the follow-up date like this:
SELECT customer_id, purchase_date, DATE_ADD(purchase_date, INTERVAL7DAY) AS follow_up_dateFROM purchases;
Instead of storing a separate follow-up date manually, the database can calculate it whenever the query runs.
Using DATE_ADD() in a WHERE Clause
DATE_ADD() can also be used to filter records.
For example, suppose you want to find sales occurring within seven days after January 1, 2024:
SELECT*FROM salesWHERE sales_date <= DATE_ADD('2024-01-01', INTERVAL7DAY);Here, MySQL first calculates:
2024-01-01 + 7 days = 2024-01-08
and then uses that date in the filter.
This becomes particularly powerful when date calculations are combined with other SQL conditions.
DATE_ADD() vs DATE_SUB()
Sometimes you need to move a date backward instead of forward.
For that, MySQL provides DATE_SUB().
Add seven days:
DATE_ADD(sales_date, INTERVAL7DAY)
Subtract seven days:
DATE_SUB(sales_date, INTERVAL7DAY)
For example:
SELECT sales_date, DATE_SUB(sales_date, INTERVAL7DAY) AS seven_days_beforeFROM sales;
A simple way to remember the difference is:
DATE_ADD() → future dateDATE_SUB() → earlier date
DATE_ADD() Isn’t the Same as Adding Months
It’s important to choose the correct interval unit.
If you want to add exactly seven calendar days:
DATE_ADD(sales_date, INTERVAL7DAY)
If you want to add three calendar months:
DATE_ADD(sales_date, INTERVAL3MONTH)
These aren’t interchangeable.
For example, adding 30 days is not necessarily the same as adding one month because months have different numbers of days.
Use DAY when your requirement is specifically based on a number of days.
Other DATE_ADD() Intervals
The DATE_ADD() function supports more than just days.
For example:
-- Add daysDATE_ADD(sales_date, INTERVAL7DAY)-- Add weeksDATE_ADD(sales_date, INTERVAL2 WEEK)-- Add monthsDATE_ADD(sales_date, INTERVAL3MONTH)-- Add yearsDATE_ADD(sales_date, INTERVAL1YEAR)
You can also use date-time units such as hours, minutes, and seconds when working with date-time values.
The basic structure remains:
DATE_ADD(date, INTERVALvalue unit)
A Quick Reference
Here are some useful examples:
-- Add 1 dayDATE_ADD(sales_date, INTERVAL1DAY)-- Add 7 daysDATE_ADD(sales_date, INTERVAL7DAY)-- Add 14 daysDATE_ADD(sales_date, INTERVAL14DAY)-- Add 30 daysDATE_ADD(sales_date, INTERVAL30DAY)-- Add 90 daysDATE_ADD(sales_date, INTERVAL90DAY)
For subtraction:
DATE_SUB(sales_date, INTERVAL7DAY)
Does DATE_ADD() Change the Original Date?
No.
This is an important point.
When you run:
SELECT DATE_ADD(sales_date, INTERVAL7DAY)FROM sales;
MySQL calculates a new value for the query result. It doesn’t automatically overwrite sales_date.
If the original value is:
2024-02-10
the table still contains:
2024-02-10
The calculated value:
2024-02-17
exists in the query result.
This makes DATE_ADD() useful for analysis because you can create calculated dates without altering your underlying data.
Final Takeaway
Adding days to a date in MySQL is straightforward with the DATE_ADD() function.
The basic syntax is:
DATE_ADD(date, INTERVAL number DAY)
For example:
SELECT sales_date, DATE_ADD(sales_date, INTERVAL7DAY) AS seven_days_laterFROM sales;
MySQL handles the calendar arithmetic automatically, including transitions between months and years.
The same technique can be used for delivery dates, customer follow-ups, deadlines, subscriptions, reporting periods, and many other date-based calculations.
And when you need to move backward rather than forward, use:
DATE_SUB(date, INTERVAL number DAY)
Once you understand DATE_ADD(), working with future dates in MySQL becomes much simpler—and you can apply the same pattern to months, weeks, years, and other time intervals.
