The simplest way: DATE_ADD() function

To add days to a date in MySQL, use the DATE_ADD() function. It takes two pieces of information: the date you're starting with, and how many days (or other time units) you want to add. The syntax is straightforward: DATE_ADD(date, INTERVAL value UNIT).

For example, if you have a date stored in a column called order_date and you want to add 7 days to it, you would write: SELECT DATE_ADD(order_date, INTERVAL 7 DAY) FROM orders; This returns a new date that is exactly one week later than the original.

MySQL also accepts other time units besides DAY. You can add HOUR, MINUTE, SECOND, MONTH, YEAR, or even combinations like MONTH and DAY together. The function always returns a date or datetime value in the same format as the input.

Key Takeaways

  • DATE_ADD() is the standard MySQL function for adding time to a date, and it works with days, hours, months, years, and other units.
  • The syntax requires three parts: the original date, the word INTERVAL, and the number and unit you want to add (for example, INTERVAL 30 DAY).
  • You can also use the plus operator (+) with INTERVAL for shorter code, like order_date + INTERVAL 7 DAY.
  • DATE_ADD() preserves the time portion if your column stores both date and time; use DATE() to strip the time if you only want the date back.

Using the plus operator as a shortcut

MySQL lets you skip the DATE_ADD() function name and use the plus operator (+) instead. The code SELECT order_date + INTERVAL 7 DAY FROM orders; does exactly the same thing as the DATE_ADD() version above. Many developers prefer this because it is shorter and reads more like math.

The plus operator works with any INTERVAL unit — HOUR, MONTH, YEAR, and so on. You can also chain multiple intervals together: order_date + INTERVAL 1 MONTH + INTERVAL 5 DAY adds one month and five days at once. This is useful when you need to calculate a important date that is, for example, 30 days plus 2 hours from now.

When your column stores both date and time

If your column is a DATETIME or TIMESTAMP type (not just DATE), DATE_ADD() returns the full datetime with the time portion unchanged. So if the original value is 2024-01-15 14:30:00 and you add 7 days, you get 2024-01-22 14:30:00 — the time stays at 14:30.

If you only want the date part back and need to discard the time, wrap the result in the DATE() function: SELECT DATE(DATE_ADD(order_date, INTERVAL 7 DAY)) FROM orders; This returns just 2024-01-22 without the time. This matters when you are comparing dates or storing the result in a DATE column that does not accept time values.

Adding negative intervals to subtract days

DATE_ADD() also accepts negative numbers, which subtracts time instead of adding it. The code DATE_ADD(order_date, INTERVAL -7 DAY) moves the date back one week. You can also use the minus operator: order_date - INTERVAL 7 DAY does the same thing.

This is useful for calculating when something started, or for finding dates in the past. For example, if you want to find all orders placed in the last 7 days, you might use WHERE order_date >= NOW() - INTERVAL 7 DAY to get everything from the past week.

Real examples in a WHERE clause

You often need to add days to a date inside a WHERE clause to filter results. For instance, if you have a due_date column and you want to find all invoices that are due within the next 14 days, you would write: SELECT * FROM invoices WHERE due_date BETWEEN NOW() AND NOW() + INTERVAL 14 DAY; This returns only invoices with a due date in the next two weeks.

Another common pattern is finding records where a important date has passed. If you have a created_at timestamp and you want to find all records older than 90 days, use: SELECT * FROM records WHERE created_at < NOW() - INTERVAL 90 DAY; This pulls everything created more than three months ago.

Handling NULL values and edge cases

If the date column contains a NULL value, DATE_ADD() returns NULL. This is usually what you want — if there is no date to begin with, there is no date to add to. However, if you need a default value instead, use the COALESCE() function: SELECT DATE_ADD(COALESCE(order_date, '2024-01-01'), INTERVAL 7 DAY) FROM orders; This uses January 1, 2024 as the starting date whenever order_date is NULL.

Be careful when adding months or years to dates that land on the last day of a month. If you add 1 month to January 31, MySQL returns February 28 (or 29 in a leap year), not March 3. This is by design, but it can surprise you if you are not expecting it. Test your queries with edge-case dates to make sure the results match what your process needs.

Frequently Asked Questions

Can I add different amounts to different rows in the same query?

Yes. You can use a column value instead of a fixed number. For example, SELECT DATE_ADD(order_date, INTERVAL days_to_add DAY) FROM orders; adds the number of days stored in the days_to_add column for each row. This is useful when different records need different calculations.

What is the difference between DATE_ADD() and DATE_SUB()?

DATE_SUB() subtracts time instead of adding it. DATE_SUB(order_date, INTERVAL 7 DAY) is the same as DATE_ADD(order_date, INTERVAL -7 DAY). Most developers use DATE_ADD() with negative numbers because it is shorter, but DATE_SUB() is clearer when you are definitely subtracting.

Does DATE_ADD() work with time zones?

DATE_ADD() does not adjust for time zones — it just adds the number of days or hours you specify. If you need to account for time zones, convert to UTC first using CONVERT_TZ(), then add the interval, then convert back. This is complex and usually handled in process code rather than in SQL.

What happens if I add months to a date and the result month has fewer days?

MySQL returns the last day of the target month. Adding 1 month to January 31 gives February 28 (or 29 in a leap year), not March 3. If you need the same day number in the next month, you have to handle it in process code.