Add Days to Date in SQL

In an SQL database, we encounter such instances where we need to manipulate and work with dates. This includes adding and subtracting durations from a given value. For example, a common task includes adding or subtracting specific days from a given date value.

In this tutorial, we will walk you through the methods and techniques that you can use to add days to a given date. We will explore these techniques for various SQL databases such as MySQL, PostgreSQL, SQL Server, and Oracle.

Sample Data

Before we dive into the various techniques, let us start by setting up a sample table and populating it with sample data for demonstration purposes. This allows us to better demonstrate each technique more clearly and effectively.

For our table, we create a new table called “orders” including a column for “order_date”. This contains the date for each order.

CREATE TABLE orders (

order_id INT PRIMARY KEY,

order_date DATE,

customer_username VARCHAR(255),

product_purchased VARCHAR(255),

price DECIMAL(10, 2),

quantity INT

);

This sample table contains information about orders that are performed in a given store. This includes the “order_date”, “customer_username”, “product_purchased”, “price”, and “quantity”.

We can then proceed to add a sample data as shown in the following:

INSERT INTO orders (order_date, customer_username, product_purchased, price, quantity)
VALUES
('2023-01-15', 'alice_s', 'Widget A', 10.99, 3),
('2023-02-20', 'smith_h', 'Widget B', 14.99, 2),
('2023-03-25', 'alice_j', 'Widget C', 19.99, 1),
('2023-04-30', 'wilson_b', 'Widget A', 10.99, 5),
('2023-05-05', 'mary_a', 'Widget B', 14.99, 4);

This should add the sample data into the “orders” table.

Add Days to Date in SQL Server

Let us start with the SQL Server. In SQL Server, we can use the DATEADD() function which allows us to add or subtract the days from a given date value.

The following example shows how to use it:

SELECT order_id, DATEADD(DAY, 7, order_date) AS new_order_date

FROM orders;

This should add seven days to the “order_date” column in the SQL Server.

It is good to keep in mind that this function is not supported in all SQL databases.

Add Days to Date in MySQL

For the sake of MySQL, we use the DATE_ADD() function which allows us to add days to a date. An example demonstration is as follows:

SELECT order_id, DATE_ADD(order_date, INTERVAL 7 DAY) AS new_order_date

FROM orders;

This should add seven days to the “order_date” column in the “orders” table. The resulting output is as follows:

order_id|new_order_date|
--------+--------------+
1| 2023-01-22|
2| 2023-02-27|
3| 2023-04-01|
4| 2023-05-07|
5| 2023-05-12|

Add Days to Date in PostgreSQL

In SQL, we use the INTERVAL keyword to add days to a given date. We can then pass the number of days that we wish to add as a string.

An example usage is as follows:

SELECT order_id, order_date + INTERVAL '7 days' AS new_order_date

FROM orders;

Similarly, this should add seven days to the specified date column.

Add Days to Date in Oracle

When it comes to Oracle database, we can use the TO_DATE function to add days to a date as demonstrated in the following query:

SELECT order_id, TO_DATE(order_date + 7, 'YYYY-MM-DD') AS new_order_date

FROM orders;

This adds seven days to the specified column.

Conclusion

In this tutorial, we walked you through the various methods and techniques of adding days to a given date in SQL databases. It is good to choose the right method for your database of choice as shown in this post.