SQL query to check if a date range is between two dates in Oracle database

2 minute read

A few days ago, we had to select records from an Oracle database table which should be inside a given date range. The difficult thing with those records was that they had a date range defined in them too.

The table was described as:

CREATE TABLE "DATEBOB" 
(	
    "ID" NUMBER(*,0) NOT NULL ENABLE, 
    "FROMDATE" DATE NOT NULL ENABLE, 
    "TODATE" DATE NOT NULL ENABLE, 
    CONSTRAINT "DATEBOB_PK" PRIMARY KEY ("ID")
)

and the records like:

ID FROMDATE TODATE
1 10-JUL-2022 10-JUL-2040
2 10-JUL-2024 10-JUL-2040
3 22-JUL-2022 23-JUL-2022
4 24-JUL-2022 25-JUL-2022
5 22-JUL-2022 22-JUL-2022

or in SQL insert statements:

INSERT INTO "DATEBOB" (ID, FROMDATE, TODATE) VALUES ('1', TO_DATE('2022-07-10', 'YYYY-MM-DD'), TO_DATE('2040-07-10', 'YYYY-MM-DD'));
INSERT INTO "DATEBOB" (ID, FROMDATE, TODATE) VALUES ('2', TO_DATE('2024-07-10', 'YYYY-MM-DD'), TO_DATE('2040-07-10', 'YYYY-MM-DD'));
INSERT INTO "DATEBOB" (ID, FROMDATE, TODATE) VALUES ('3', TO_DATE('2022-07-22', 'YYYY-MM-DD'), TO_DATE('2022-07-23', 'YYYY-MM-DD'));
INSERT INTO "DATEBOB" (ID, FROMDATE, TODATE) VALUES ('4', TO_DATE('2022-07-24', 'YYYY-MM-DD'), TO_DATE('2022-07-25', 'YYYY-MM-DD'));
INSERT INTO "DATEBOB" (ID, FROMDATE, TODATE) VALUES ('5', TO_DATE('2022-07-22', 'YYYY-MM-DD'), TO_DATE('2022-07-22', 'YYYY-MM-DD'));

In order to select the records which are valid from the current date up to 3 days in the future, you can do it with this query:

select *
from datebob
where 
(trunc(sysdate) BETWEEN fromdate AND todate)
or ((trunc(sysdate)+3) BETWEEN fromdate AND todate) ;

example output when current date is 2022-07-22:

  ID FROMDATE  TODATE   
---- --------- ---------
   1 10-JUL-22 10-JUL-40
   3 22-JUL-22 23-JUL-22
   4 24-JUL-22 25-JUL-22
   5 22-JUL-22 22-JUL-22

In order to select the records which are valid from a specific date current date (let us say 2022-07-24) up to 3 days in the future, you can do it with this query:

select *
from datebob
where 
(TO_DATE('2022-07-24', 'YYYY-MM-DD') BETWEEN fromdate AND todate)
or ((TO_DATE('2022-07-24', 'YYYY-MM-DD')+3) BETWEEN fromdate AND todate) ;

and the output looks like the following:

   ID FROMDATE  TODATE   
 ---- --------- ---------
    1 10-JUL-22 10-JUL-40
    4 24-JUL-22 25-JUL-22

Note that in order to add days in a DATE column, you just need to use the plus sign and the number of days to add. There is no specific function like DATE_ADD of MariaDB which offers different intervals, like seconds, months, etc.

Comments

You May Also Enjoy