Re: DatePart

"Jarl Hermansson" <jarl_at_mimer.com> wrote in message news:2aaa8682.0402191352.553a0263_at_posting.google.com...
> "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

SQL-99 specifies nothing of worth or value. Received on Fri Feb 20 2004 - 00:13:00 CET