How to add days to a date with SQL

Problem

You're working with SQL and you have a date value. You're required to add a certain number of days to this date.

Sample Data

Let's consider a simple table Orders for this problem.

CREATE TABLE Orders (
    OrderID int,
    ProductName varchar(255),
    OrderDate date
);

And let's assume we have the following data:

INSERT INTO Orders (OrderID, ProductName, OrderDate)
VALUES (1, 'Apple', '2022-01-01'),
       (2, 'Banana', '2022-02-01'),
       (3, 'Cherry', '2022-03-01');

Now, we want to add 10 days to each OrderDate.

MySQL Solution

SELECT OrderID, ProductName, DATE_ADD(OrderDate, INTERVAL 10 DAY) as NewOrderDate
FROM Orders;

PostgreSQL Solution

SELECT OrderID, ProductName, OrderDate + INTERVAL '10 day' as NewOrderDate
FROM Orders;

Explanation

Both MySQL and PostgreSQL provide functions to perform date arithmetic.

In MySQL, the DATE_ADD() function is used. It takes two arguments: the original date and an interval value which specifies the amount to add to the date. The interval value is a combination of number and unit. In this case, we used 10 DAY to represent 10 days.

In PostgreSQL, the + INTERVAL 'n day' is used. It is similar to MySQL, but the syntax is a bit different. This operator adds a specified time interval 'n day' to the original date.

In both solutions, a new column NewOrderDate is produced in the result set which contains the updated dates.