Filter out date records which don't fall within or span a date range

I have records which have specific time spans. The need is to select records which adhere to the following three rules (read them as or conditions) concerning a target time span:

• Record's Begin date can begin within the target time span and end date does not matter.

• Record's End date falls with the target time span and the start date is inconsequential.

• Record's dates actually span the target time.

So basically a query should return all records which start, end or span a target time frame.

Is there a more efficient way in Oracle 11g of doing the above rules than what I currently have stated in the SQL below?

I have created a test SQL Fiddle Test for the below schema and SQL

Schema

CREATE TABLE dates_test (
  id         NUMBER,
  hint       VarChar(20),
  BEGIN_DATE DATE,
  END_DATE DATE
);
-- If the span is Mar 6 to April 6 then 2, 3 and 4 are valid -
INSERT INTO dates_test VALUES (1, 'exclude before', TO_DATE('01-JAN-2014','DD-MON-YYYY'), TO_DATE('05-JAN-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (2, 'spans',          TO_DATE('05-MAR-2014','DD-MON-YYYY'), TO_DATE('08-SEP-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (3, 'Starts within',  TO_DATE('07-MAR-2014','DD-MON-YYYY'), TO_DATE('07-SEP-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (4, 'Ends within',    TO_DATE('01-JAN-2014','DD-MON-YYYY'), TO_DATE('07-MAR-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (5, 'exclude after',  TO_DATE('06-JUN-2014','DD-MON-YYYY'), TO_DATE('07-SEP-2014','DD-MON-YYYY'));
INSERT INTO dates_test VALUES (6, 'within OK',  TO_DATE('08-MAR-2014','DD-MON-YYYY'), TO_DATE('09-MAR-2014','DD-MON-YYYY'));

Here is the SQL which returns what I need but can it be improved?

```
select * from
dates_test t0
where
( -- Starts Within range
( t0.BEGIN_DATE
BETWEEN TO_DATE('03-06-2014', 'mm-dd-yyyy')
AND TO_DATE('04-06-2014', 'mm-dd-yyyy')
)
OR -- Ends within range
(
t0.END_DATE
BETWEEN TO_DATE('03-06-2014', 'mm-dd-yy

So, My first answer messed up (Can I blame it on incomplete test data?)

That's a good thing to note, that your should design your tests to cover the situations that are significant to you. Because your tests were incomplete, when I ran and modified your code, and got the 'same' results, I assumed the code was good. I never validated your test cases (my fault).

This re-work made me realize that your data would be better served with a negative test case. What you want to test for is exclusionary ranges. If you can assume your data is valid (it is, right), then you want to just test those cases where there is no intersection.

There is no intersection if the test date ends before the data's date, or the data's date ends before the test date. This simplifies the query to:

select *
from dates_test t0
where not (
      END_DATE  TO_DATE('04-06-2014', 'mm-dd-yyyy')

As was pointed out to me (very nicely, I may add), the above logic can be simplified further using 'basic' boolean logic to:

select *
from dates_test t0
where END_DATE >= TO_DATE('03-06-2014', 'mm-dd-yyyy')
  and BEGIN_DATE <= TO_DATE('04-06-2014', 'mm-dd-yyyy'))

which is my final answer (thanks @mjolka)

Using your (revised) SQL fiddle, the above query produces (note that your revised data has two records with id 5):

| ID | HINT | BEGIN_DATE | END_DATE |
|----|---------------|--------------------------------|----------------------------------|
| 2 | spans | March, 05 2014 00:00:00+0000 | September, 08 2014 00:00:00+0000 |
| 3 | Starts within | March, 07 2014 00:00:00+0000 | September, 07 2014 00:00:00+0000 |
| 4 | Ends within | January, 01 2014 00:00:00+0000 | March, 07 2014 00:00:00+0000 |
| 5 | within OK | March, 08 2014 00:00:00+0000 | March, 09 2014 00:00:00+0000 |


StackExchange Code Review Q#67260, answer score: 10