Re: DatePart

"Bob Hairgrove" <wouldnt_you_like_at_to_know.com> wrote in message news:5j59301akoevrf2ecl5734hr6l2s21qgk4_at_4ax.com...
> On Tue, 17 Feb 2004 15:34:01 +0100, Portroe <fb_at_oooi.com> wrote:
>
> >Hi All,
> >
> >I am currently struggling with the use of the DatePart() function,
> >
> >for instance if I have a check in and check out date in my hotel
> >datebase, how can I find all bookings I have for may,
> >
> >A pointer to an online tutorial would be appreciated,
>
> Sounds more like a MSAccess/VBA question to me. The Month(<some date>)
> function would be easier to use here, but it's also VBA and therefore
> does not work for anything but queries executing within Access itself,
> as opposed to queries over an ODBC connections to an Access database,
> or ASP web pages, for example.
>
> If you store check-in and check-out dates in separate columns for the
> same booking, then you can use "BETWEEN ... AND" syntax which is also
> portable ANSI-SQL syntax. This would give you a booking for a stay
> which is entirely within the month of May. However, you will typically
> have some bookings which start on April 30th and end on May 2, for
> example, or cross the end of the month from May to June. Also, there
> is the (remote?) possibility that someone might stay for more than a
> month.
>
> To catch all these, your "where" clause might look like this:
>
> WHERE (checkin BETWEEN #05/01/2003# AND #05/31/2003#)
> OR (checkin <= #05/01/2003# AND checkout >= #05/01/2003#)
> OR (checkin <= #05/31/2003# AND checkout >= #05/31/2003#)
> etc.

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# Received on Thu Feb 19 2004 - 16:47:31 CET