Re: DatePart

"Bob Badour" <bbadour_at_golden.net> wrote in message news:wPqdneUlEe9YR6nd4p2dnA_at_golden.net...
> "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#

What language is this?

The original post specified the *datepart()* function. This is a standard function in SQL server.

It requires nothing but the following:
DATEPART (datepart, date)

In the above instance the record selection qualifier simply becomes:

where DATEPART (mm, checkin_date) = 5
(mm being month)

Samples available in ms Book OnLine. Received on Fri Feb 20 2004 - 04:24:38 CET