Adding Days to Dates in MySQL: The DATE_ADD Function and Alternatives

Use DATE_ADD() to add days to a date in MySQL

The simplest way to add days to a date in MySQL is the DATE_ADD() function. It takes two arguments: the date you're starting with, and an interval that tells MySQL how much time to add. For a single day, the syntax looks like this:

SELECT DATE_ADD('2024-01-15', INTERVAL 1 DAY);

This returns 2024-01-16. You can use this same function in UPDATE statements, WHERE clauses, or anywhere else you write SQL. If you're adding days to a column in your table rather than a fixed date, replace the quoted date with your column name — for example, DATE_ADD(order_date, INTERVAL 1 DAY).

The INTERVAL keyword is what makes DATE_ADD flexible. You can add not just days, but hours, minutes, weeks, months, or years by changing the unit. INTERVAL 7 DAY adds a week, INTERVAL 3 MONTH adds three months, and so on.

Key Takeaways

DATE_ADD() is the standard MySQL function for adding time to a date, and it works in SELECT statements, UPDATE statements, and WHERE clauses.
The syntax is DATE_ADD(date_column, INTERVAL number UNIT), where UNIT can be DAY, WEEK, MONTH, YEAR, HOUR, MINUTE, or SECOND.
The + operator works as a shorthand for adding days only: SELECT order_date + INTERVAL 1 DAY works the same as DATE_ADD(order_date, INTERVAL 1 DAY).
DATE_ADD() handles month and year boundaries automatically — adding one day to January 31 correctly returns February 1, and adding one month to January 31 returns February 28 or 29.

Using DATE_ADD() in a SELECT statement

When you want to retrieve a date that is one day in the future without changing your actual data, use DATE_ADD() in a SELECT statement. This is useful for calculating delivery dates, due dates, or any deadline based on an existing date:

SELECT order_id, order_date, DATE_ADD(order_date, INTERVAL 1 DAY) AS next_day FROM orders;

This query returns three columns: the order ID, the original order date, and a new column called next_day that shows the date one day later. The original order_date column in your table stays unchanged. You can name the result column anything you want by using AS — AS delivery_date, AS due_date, or whatever makes sense for your data.

If you need to add multiple different intervals in the same query, you can nest DATE_ADD() calls or use it multiple times. For example, DATE_ADD(DATE_ADD(order_date, INTERVAL 1 DAY), INTERVAL 2 HOUR) adds one day and two hours.

Using DATE_ADD() in an UPDATE statement

To permanently change a date column by adding days to it, use DATE_ADD() in an UPDATE statement. This modifies the actual data in your table:

UPDATE orders SET delivery_date = DATE_ADD(delivery_date, INTERVAL 1 DAY) WHERE status = 'pending';

This query finds all rows where the status is 'pending' and adds one day to their delivery_date column. Be careful with UPDATE statements — they change your real data. It's a good idea to run a SELECT query first with the same WHERE clause to see which rows will be affected, then run the UPDATE.

You can also use DATE_ADD() to update a column based on a different column. For example, UPDATE orders SET reminder_date = DATE_ADD(order_date, INTERVAL 7 DAY) sets the reminder_date to one week after the order_date for all rows.

The + operator as a shorthand for adding days

MySQL also lets you use the + operator to add days to a date, but only for days — not for other time units. This syntax is shorter but less flexible:

SELECT order_date + INTERVAL 1 DAY FROM orders;

This does the same thing as DATE_ADD(order_date, INTERVAL 1 DAY). Some developers prefer this syntax because it's shorter, while others prefer DATE_ADD() because it's more explicit about what the function does. Both work identically in MySQL. If you need to add something other than days — hours, weeks, or months — you must use DATE_ADD() or the similar DATE_SUB() function.

Handling month and year boundaries

When you add days to a date that falls near the end of a month, MySQL handles the boundary correctly. Adding one day to January 31 gives you February 1, not an invalid date. The same applies when adding months or years:

If you add one month to January 31, MySQL returns February 28 (or February 29 in a leap year), not an error. This automatic adjustment prevents you from accidentally creating invalid dates. If you add one month to January 15, you get February 15 — the same day of the month, one month later.

This behavior is consistent across all time units. Adding one year to February 29, 2020 (a leap year) returns February 28, 2021, because 2021 is not a leap year. Understanding this automatic adjustment helps you predict what DATE_ADD() will return when working with dates near month or year boundaries.

Using DATE_ADD() in WHERE clauses

You can also use DATE_ADD() to filter rows based on calculated dates. For example, to find all orders where the delivery date is more than one week away:

SELECT * FROM orders WHERE DATE_ADD(order_date, INTERVAL 7 DAY) > NOW();

This query calculates what the date would be seven days from each order_date, then compares it to the current date and time using NOW(). Only rows where that calculated date is in the future are returned. This is useful for finding orders that still have time before a deadline, or for identifying orders that should have been delivered by now.

You can combine DATE_ADD() with other comparison operators like <, >=, <=, and = to build more complex filters. For example, WHERE DATE_ADD(order_date, INTERVAL 30 DAY) = CURDATE() finds orders where the 30-day mark is today.

Frequently Asked Questions

What's the difference between DATE_ADD() and DATE_SUB()?

DATE_ADD() adds time to a date, while DATE_SUB() subtracts time. The syntax is identical: DATE_SUB(order_date, INTERVAL 1 DAY) returns the date one day earlier. Use DATE_ADD() for future dates and DATE_SUB() for past dates, though you can also use DATE_ADD() with a negative number if you prefer.

Can I add fractional days, like 0.5 days?

DATE_ADD() works with whole numbers only. To add half a day (12 hours), use DATE_ADD(order_date, INTERVAL 12 HOUR) instead. If you need more precision, work with HOUR, MINUTE, or SECOND units rather than trying to use decimal days.

What happens if I add days to a NULL date?

DATE_ADD() returns NULL if the date argument is NULL. If your column contains NULL values and you use DATE_ADD() in a SELECT, those rows will show NULL in the result. Use COALESCE() or IF() to provide a default value if you need to handle NULLs differently.

Can I add days based on a value in another column?

Yes. UPDATE orders SET delivery_date = DATE_ADD(order_date, INTERVAL days_to_add DAY) adds the number of days stored in the days_to_add column to each order_date. This works as long as the column contains a number.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.