Add days to Date in Oracle with examples

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.

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′.

add days using plus operator in Oracle
add days using plus operator in Oracle

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

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.

Add days using Interval data type in Oracle
Add days using Interval data type in Oracle

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.

To_date function with Interval in Oracle
To_date function with Interval in Oracle

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.

Once the Food_items table created successfully, we are inserting the following records into it.

Lets select the inserted records from the Food_Items table.

Create and Select table in Oracle
Create and Select table in Oracle

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

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

Add days in Where Clause of Oracle query
Add days in Where Clause of Oracle query