It is relatively simple to check that a single, specific date falls within a range of other dates. Suppose we have an existing hotel room booking from 16th September to 20th September, and someone wants to know if our room is free for the night of 19th September:

SQL> select
 case when to_date('19-SEP-2012','DD-MON-YYYY') between to_date('16-SEP-2012','DD-MON-YYYY') and to_date('20-SEP-2012','DD-MON-YYYY')
 then 'Overlap'
 else 'No Overlap'
end as CROSSOVER
from dual;

CROSSOVER
----------
Overlap

The between function will do the job nicely. It gets more interesting, though, when the proposed new booking is for, say, the 21st September to 26th September: now you have the problem of checking two date ranges against each other… and, in theory, any part of the new date range might overlap any part of the old range. How to detect that full or partial overlap?

One approach is to consider the three logical possibilities for an overlap:

• Proposed booking start date is between the existing booking’s start and end dates
• Proposed booking end date is between the existing booking’s start and end dates
• Proposed booking start date is before the existing booking’s start date AND the proposed end date is after the existing booking’s end date

Only if none of those three conditions is met would the proposed new booking be acceptable (i.e., would we say there is no overlap of the two date ranges). One way of coding that in SQL, for example, might be:

With Condition1 as 
(select case when to_date('&&ProposedStart','DD-MON-YYYY') between to_date('16-SEP-2012','DD-MON-YYYY') and to_date('20-SEP-2012','DD-MON-YYYY')
 then 1 else 0 end as FAILIFONE from dual),
Condition2 as 
(select case when to_date('&&ProposedEnd','DD-MON-YYYY') between to_date('16-SEP-2012','DD-MON-YYYY') and to_date('20-SEP-2012','DD-MON-YYYY')
 then 1 else 0 end as FAILIFONE from dual),
Condition3 as 
(select case when to_date('&&ProposedStart','DD-MON-YYYY') < to_date('16-SEP-2012','DD-MON-YYYY') AND to_date('&&ProposedEnd','DD-MON-YYYY') > to_date('20-SEP-2012','DD-MON-YYYY')
 then 1 else 0 end as FAILIFONE from dual) 
select case when sum(failifone)>0 then 'Overlap' else 'No Overlap' end as crossover from ( 
Select failifone from condition1
union 
Select failifone from condition2
union 
Select failifone from condition3);

If you run that code and supply 21-SEP-2012 and 26-SEP-2012 as the proposed start and end dates, you’ll find no conflict between the proposed and existing bookings:

Enter value for proposedstart: 21-SEP-2012
Enter value for proposedend: 26-SEP-2012

CROSSOVER
----------
No Overlap

Supply different dates, though, and you should get appropriate responses. For example, for the date range 1st September to 30th October -which we know immediately overlap- you’ll get this response:

Enter value for proposedstart: 01-SEP-2012
Enter value for proposedend: 31-OCT-2012

CROSSOVER
----------
Overlap

You’ll similarly find that a proposed booking of 20th September to 26th September fails, because the overlap on the 20th September is detected… and so on.

Where it gets interesting, though, is that when this subject comes up in forums etc. it is often mentioned that Oracle has an OVERLAPS function (which was first introduced in version 8.1, but has never been documented… which makes using it problematic!) The syntax for OVERLAPS is a lot simpler than my coding efforts above:

SQL> With overlaps as
 (select 'Y' as overlap_flag from dual where
 (to_date('16/09/2012','DD/MM/YYYY'), to_date('20/09/2012','DD/MM/YYYY'))
 overlaps
 (to_date('21/09/2012','DD/MM/YYYY'), to_date('26/09/2012','DD/MM/YYYY'))
 ),
fillers as
 (select 'Y' as dummy from dual)
 2 3 4 5 6 7 8 9 select case when o.overlap_flag='Y' then 'Overlap Detected' else 'No Overlap' end as Overlap
 10 from
 11 overlaps o, fillers f
 12 where o.overlap_flag(+) = f.dummy;

OVERLAP
----------------
No Overlap

Here, I’m again comparing a 16th-20th original booking with a proposed booking for 21st-26th… and, quite rightly, no overlap in the date ranges is detected. But this minor revision shows a problem:

SQL> With overlaps as
 2 (select 'Y' as overlap_flag from dual where
 3 (to_date('16/09/2012','DD/MM/YYYY'), to_date('20/09/2012','DD/MM/YYYY'))
 4 overlaps
 5 (to_date('20/09/2012','DD/MM/YYYY'), to_date('26/09/2012','DD/MM/YYYY'))
 6 ),
 7 fillers as
 8 (select 'Y' as dummy from dual)
 9 select case when o.overlap_flag='Y' then 'Overlap Detected' else 'No Overlap' end as Overlap
 10 from
 11 overlaps o, fillers f
 12 where o.overlap_flag(+) = f.dummy;

OVERLAP
----------------
No Overlap

Here, I’m comparing an original 16th-20th with a proposed 20th-26th. You and I might, I think, say that the 20th collides and that an overlap should be declared… but the OVERLAPS function begs to disagree.

You might think this has to do with days starting/ending at midnight, and so on. But you can modify the code slightly to get to-the-second precision, like so:

SQL> With overlaps as
 2 (select 'Y' as overlap_flag from dual where
 3 (to_date('16/09/2012','DD/MM/YYYY'), to_date('20/09/2012 23:59:46','DD/MM/YYYY HH24:MI:SS'))
 4 overlaps
 5 (to_date('20/09/2012 23:59:46','DD/MM/YYYY HH24:MI:SS'), to_date('26/09/2012','DD/MM/YYYY'))
 6 ),
 7 fillers as
 8 (select 'Y' as dummy from dual)
 9 select case when o.overlap_flag='Y' then 'Overlap Detected' else 'No Overlap' end as Overlap
 10 from
 11 overlaps o, fillers f
 12 where o.overlap_flag(+) = f.dummy;

OVERLAP
----------------
No Overlap

Here, I’ve asked to compare an ending date of 11:59 and 46 seconds PM with a proposed start time of 11:59 and 46 seconds PM… and again, the function declares the two don’t overlap.

Interestingly, though, my original ‘hand-coded’ SQL, if slightly adjusted to allow for hours, minutes and second precision on the relevant start/end dates does declare it an overlap. Here’s my modified code:

With Condition1 as 
(select case when to_date('&&ProposedStart','DD-MON-YYYY HH24:MI:SS') between to_date('16-SEP-2012','DD-MON-YYYY') and to_date('20-SEP-2012 23:59:46','DD-MON-YYYY HH24:MI:SS')
 then 1 else 0 end as FAILIFONE from dual),
Condition2 as 
(select case when to_date('&&ProposedEnd','DD-MON-YYYY') between to_date('16-SEP-2012','DD-MON-YYYY') and to_date('20-SEP-2012 23:59:46','DD-MON-YYYY HH24:MI:SS')
 then 1 else 0 end as FAILIFONE from dual),
Condition3 as 
(select case when to_date('&&ProposedStart','DD-MON-YYYY HH24:MI:SS') < to_date('16-SEP-2012','DD-MON-YYYY') AND to_date('&&ProposedEnd','DD-MON-YYYY') > to_date('20-SEP-2012 23:59:46','DD-MON-YYYY HH24:MI:SS')
 then 1 else 0 end as FAILIFONE from dual) 
select case when sum(failifone)>0 then 'Overlap' else 'No Overlap' end as crossover from ( 
Select failifone from condition1
union 
Select failifone from condition2
union 
Select failifone from condition3);

This just allows the proposed start time of the new booking to be specified to the second, and knows the end-time of the existing booking to the second, too. Plug in the relevant proposed start and end times, and you get this:

SQL> With Condition1 as
 2 (select case when to_date('&&ProposedStart','DD-MON-YYYY HH24:MI:SS') between to_date('16-SEP-2012','DD-MON-YYYY') and to_date('20-SEP-2012 23:59:46','DD-MON-YYYY HH24:MI:SS')
 3 then 1 else 0 end as FAILIFONE from dual),
 4 Condition2 as
 5 (select case when to_date('&&ProposedEnd','DD-MON-YYYY') between to_date('16-SEP-2012','DD-MON-YYYY') and to_date('20-SEP-2012 23:59:46','DD-MON-YYYY HH24:MI:SS')
 6 then 1 else 0 end as FAILIFONE from dual),
 7 Condition3 as
 8 (select case when to_date('&&ProposedStart','DD-MON-YYYY HH24:MI:SS') < to_date('16-SEP-2012','DD-MON-YYYY') AND to_date('&&ProposedEnd','DD-MON-YYYY') > to_date('20-SEP-2012 23:59:46','DD-MON-YYYY HH24:MI:SS')
 9 then 1 else 0 end as FAILIFONE from dual)
 10 select case when sum(failifone)>0 then 'Overlap' else 'No Overlap' end as crossover from (
 11 Select failifone from condition1
 12 union
 13 Select failifone from condition2
 14 union
 15 Select failifone from condition3);

Enter value for proposedstart: 20-SEP-2012 23:59:46
Enter value for proposedend: 26-SEP-2012

CROSSOVER
----------
Overlap

I guess there’s a philosophical question to be addressed here. If my original range is (say) the numbers from 5 to 10, does the number 10 fall “between” those? Oracle says it does:

SQL> select case when 10 between 5 and 10 then 'True' else 'False' end from dual;

CASEW
-----
True

…though you might well argue that only the numbers 6, 7, 8 and 9 are legitimately “between” the comparison number range.

Similarly, if your starting range are the letters “e” to “m”, does the letter “e” fall between those? Again, Oracle says yes:

SQL> select case when 'e' between 'e' and 'm' then 'True' else 'False' end from dual;

CASEW
-----
True

…though whether the start and end point of a range can legitimately be said to be “between” anything is, it seems to me, at least questionable.

Anyway, the kicker here is that Oracle is philosophically inconsistent on the matter: the OVERLAPS function considers that a date, Z, does not overlap a date range X->Z, whereas the BETWEEN function does. Which is something you might want to be aware of… and is probably a good reason to steer clear of undocumented functions!