Select all rows where new dateStart and new DateEnd not Intersection. Oracle

Question

I have table SOME_TABLE

id   date_start     date_end
0    05.01.2017     09.01.2017
...

I need select for get all rows where my new date_start and new date_end with Intersection.

if

new date_start = 01.01.2017 and new date_end  10.01.2017
or new date_start = 06.01.2017 and new date_end  06.01.2017
or new date_start = 01.01.2017 and new date_end  06.01.2017
or new date_start = 08.01.2017 and new date_end  10.01.2017

I need get this rows and if

new date_start = 22.12.2016 and new date_end  31.12.2016
or new date_start = 15.01.2017 and new date_end 22.01.2017

I need not select this rows.

I create select but it not work

select *
  from SOME_TABLE t
 where (to_date('02.04.2017', 'dd.MM.yyyy') >= t.date_start AND
       to_date('02.04.2017', 'dd.MM.yyyy') <= t.date_end)
    OR to_date('11.04.2017', 'dd.MM.yyyy') > t.date_start

if date not Intersection this select return rows does not matter

EDIT if this answer:

If start and end dates can be out of order, i.e., if it is possible that startA > endA or startB > endB, then you also have to check that they are in order, so that means you have to add two additional validity rules: (StartA <= EndB) and (StartB <= EndA) and (StartA <= EndA) and (StartB <= EndB) or: (StartA <= EndB) and (StartA <= EndA) and (StartB <= EndA) and (StartB <= EndB) or, (StartA <= Min(EndA, EndB) and (StartB <= Min(EndA, EndB)) or: (Max(StartA, StartB) <= Min(EndA, EndB)

How can I Remake my select like it?

Possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — Mike Nakis, Apr 19 '17 at 09:29

MT0 · Answer 1 · 2017-04-19T10:10:27.077

Two date ranges intersect if one starts before the other ends (start1 < end2) and it also ends after the other starts (end1 > start2). So, this will get the rows that do not instersect with other rows in your table:

SELECT *
FROM   some_table t
WHERE  NOT EXISTS (
  SELECT 1
  FROM   some_table x
  WHERE  x.start_date < t.end_date
  AND    x.end_date   > t.start_date
)

If you also need to consider ranges intersecting if one starts at the same date and time as the other ends then change the conditions from </> to <=/>=.

If you just want to consider rows that intersect / do not intersect a given date range then to get the non-intersecting ranges:

SELECT *
FROM   some_table
WHERE  end_date   <= :your_start_date
OR     start_date >= :your_end_date

And to get the intersecting ranges:

SELECT *
FROM   some_table
WHERE  end_date   > :your_start_date
AND    start_date < :your_end_date

Update:

If start and end dates can be out of order

Use LEAST( start_date, end_date ) to get the lower value and GREATEST( start_date, end_date ) to get the higher value. I.e. you can change the first query to:

SELECT *
FROM   some_table t
WHERE  NOT EXISTS (
  SELECT 1
  FROM   some_table x
  WHERE  LEAST( x.start_date, x.end_date )    < GREATEST( t.start_date, t.end_date )
  AND    GREATEST( x.start_date, x.end_date ) > LEAST( t.start_date, t.end_date )
)

Select all rows where new dateStart and new DateEnd not Intersection. Oracle

1 Answers1