CODEWITHSUNDEEP
sqloracle-databaseplsql
Victor
Victor

Reputation: 333

Finding records between two Dates column in Oracle Sql

I have a table which has two column Start_Date and End_Date. I want to insert a new record into this table but before inserting, need to check if there is a record within that range of Start Date and End Date.

 ID    Start_Date      End_Date
 1     01/01/2022      01/15/2022
 2     01/16/2022      01/20/2022
 3     02/01/2022      02/28/2022
 4     01/27/2022      01/29/2022

I want to Insert a new record with Say

  Start_Date as 01/18/2022 and end date as 03/31/2022.

Is it possible to check using a sql to find record in that date range or we need to write plsql

Upvotes: 0

Views: 726

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

You may check for an overlapping range and refrain from inserting if one be present:

INSERT INTO yourTable (Start_Date, End_Date)
SELECT date '2022-01-18', date '2022-03-31'
WHERE NOT EXISTS (
    SELECT 1
    FROM yourTable
    WHERE date '2022-01-18' < End_Date AND date '2022-03-31' > Start_Date
);

Upvotes: 2

Related Questions