Add Days to Date in SQL
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.
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:
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:
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:
FROM orders;
This should add seven days to the “order_date” column in the “orders” table. The resulting output is as follows:
--------+--------------+
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:
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:
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.
Source: linuxhint.com