How to Add Days to a Date in MySQL

Problem:

You’d like to add days to a date in a MySQL database.

Example:

Our database has a table named trip with data in the columns id, city, and start_date.

Let’s add two days to the start dates and get the trip cities with the new start dates.

Solution:

We’ll use the DATE_ADD() function. Here’s the query you’d write:

SELECT 
  city,
  DATE_ADD(start_date, INTERVAL 2 DAY) AS later_date
FROM trip;

Here’s the result of the query:

Discussion:

Use the DATE_ADD() function if you want to increase a given date in a MySQL database. In our example, we increased each start date by two days.

This function takes two arguments. The first argument is the date or datetime you want to change. This argument can be the name of a date/datetime column or an expression returning a date/datetime value. In our example, we used the start_date column, which is of the date data type.

The second argument is the INTERVAL operator followed by an integer indicating the number of units (in our example, 2) and the date/time unit to add (in our example, DAY). Other units you can include MINUTE, HOUR, MONTH, YEAR, and more. You can find more about interval units in the Interval Expression and Unit Arguments section of the MySQL documentation.

DATE_ADD() returns a new date or datetime value. In our example, the trip to Houston has a new start date: 2023-07-17.