Re: DatePart

"Bob Badour" <bbadour_at_golden.net> wrote in message news:<wPqdneUlEe9YR6nd4p2dnA_at_golden.net>...
>
> Overlaps is more concisely and recognizably expressed as A starts before B
> ends and ends after B starts. ie:
>
> checkin <= #05/31/2003# and checkout >= #05/01/2003#

Note that SQL-99 specifies OVERLAPS in a kind of unintuitive way, since start dates and end dates are treated differently:

(S1 > S2 AND NOT (S1 >= T2 AND T1 >= T2)) OR
(S2 > S1 AND NOT (S2 >= T1 AND T2 >= T1)) OR
(S1 = S2 AND (T1 <> T2 OR T1 = T2))

This means that if two periods have just one single day in common, they overlap as long as this day is not the ending day of one of the periods.

Example same start date:
SQL>select case when (date '2004-02-01', date '2004-02-02') SQL& overlaps (date '2004-02-01', date '2004-02-01') then 1 SQL& else 0 end from onerow_table;  

==
 1

                  1 row found

Example same end date:
SQL>select case when (date '2004-02-01', date '2004-02-02') SQL& overlaps (date '2004-02-02', date '2004-02-02') then 1 SQL& else 0 end from onerow_table;  

==
 0

                  1 row found

Regards,
Jarl Received on Thu Feb 19 2004 - 22:52:33 CET