Articles Cookbook
Back to list
MySQL

How to Add Days to a Date in MySQL

Database:

Operators:

DATE_ADD(), INTERVAL

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.

idcitystart_date
1Chicago2019-06-22
2Houston2019-07-15
3Dallas2019-08-30
4Austin2019-09-23

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:

citystart_date
Chicago2019-06-24
Houston2019-07-17
Dallas2019-09-01
Austin2019-09-25

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). 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: ‘2019-07-17’.

Recommended courses:

Recommended articles:

See also:

go to top