Filter out date records which don't fall within or span a date range
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 &lt; TO_DATE('03-06-2014', 'mm-dd-yyyy')
   or BEGIN_DATE &gt; 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 &gt;= TO_DATE('03-06-2014', 'mm-dd-yyyy')
  and BEGIN_DATE &lt;= 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 |