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.

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

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.

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.

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.

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.

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

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