Add days to Date in Oracle with examples
Contents
Add days to Date in Oracle
Add days is required to calculate the future dates in Database. Oracle allows us to add days to Date using plus(+) operator and Interval data type.
Plus(+) operator to add days
Assume that current date is ’06/06/2020′ (DD/MM/YYYY). Lets add 2 days to current date in Oracle.
|
1 |
SELECT CURRENT_DATE + 2 FROM dual; |
The CURRENT_DATE functions returns the today’s date as ’06/06/2020′. As we mentioned the + 2 with the current date, it added the 2 days and returned the output as ’08/JUN/2020′.
Interval data type to add days
Interval data type used to specify the intervals such as Day,Month,Year while adding the values to Date. If we want to add days, we can mention the Interval ‘2’ day to the date values. Similarly we can give Interval ‘n’ Month and Interval ‘n’ Year for adding month and year respectively.
- Interval ‘n’ Day – To add n number of days
- Interval ‘n’ Month – To add n number of months
- Interval ‘n’ Year – To add n number of Years
|
1 |
SELECT CURRENT_DATE + INTERVAL '2' DAY FROM dual; |
As we mentioned Interval ‘2’ day in the above query, two days added to Current date (06/06/2020) and returned the output as 08-JUN-20.
If we give the Date value in String, we need to convert the string to Date using To_Date function and then we can add days to Date using Interval.
|
1 |
SELECT TO_DATE('06/06/2020','DD/MM/YYYY') + INTERVAL '2' DAY FROM dual; |
Add days to date in Oracle where clause
In some cases, we want to add days in the Where clause of Oracle query. Lets create the Food_Items table to demonstrate the scenario and add days usage.
|
1 2 3 4 5 6 7 |
CREATE TABLE Food_Items( item_id NUMBER, item_name VARCHAR2 ( 255 ) NOT NULL, manufacture_date DATE NOT NULL, -- DATE variable expiry_date DATE NOT NULL, -- DATE variable PRIMARY KEY ( item_id ) ); |
Once the Food_items table created successfully, we are inserting the following records into it.
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO Food_Items values (176,'Milk',DATE '2019-12-15',DATE '2020-12-16'); INSERT INTO Food_Items values (128,'Pizza',DATE '2019-12-15',DATE '2020-12-17'); INSERT INTO Food_Items values (189,'Fruit_Juice',DATE '2019-12-14',DATE '2020-12-30'); INSERT INTO Food_Items values (153,'Potato_Chips',DATE '2019-12-08',DATE '2020-12-15'); |
Lets select the inserted records from the Food_Items table.
|
1 |
SELECT * FROM Food_Items; |
Example for Add days in where clause
From the Food_Items table, we want to select the items which are going to expire in the next 3 days from 15-DEC-20.
We can get the required records by using the add days and between function for the Expire_Date column as below
|
1 2 3 4 |
SELECT * FROM Food_Items WHERE Expiry_Date BETWEEN DATE '2020-12-15'AND DATE '2020-12-15' + 3 |
The following food items are going to expire in the next 3 days from 15-Dec-20. The 3 days such as 15-DEC-20, 16-DEC-20 and 17-DEC-20 are filtered in the table.
Output
