Leyne, Sean wrote:

>
>
> Lee,
>
> > I'm wondering what would be the best way to pull a record from the
> > database based on a StartTime and EndTime (both time fields) and a
> passed
> > value of Time.
> > I know the BETWEEN command, but I'm wondering how it will be
> effected by
> > the way time is circular.
>
> BETWEEN is not the right answer.
>
> > For instance, if the StartTime is 3:00 PM and the EndTime is 1:00 AM
> and
> > the current time is 5:00 PM, I don't think it will return the correct
> > values since it'll see the EndTime, 1:00 AM as part of the same day,
> but
> > earlier.
>
> The SQL is:
>
> SELECT
> ...
> WHERE
> StartTime_Field => :TargetTime
> AND (
> EndTime_Field <= :TargetTime
> OR EndTime_Field < StartTime_Field
> )
>
> > I'm assuming the best way to handle will be within the application
> itself.
> > My first impulse is to build a flag into the app that the user can
> specify
> > that the end time value extends to the following day.
>
> The above SQL eliminates the need for the flag, although I would
> recommend storing a true Date/Time values for the Start and End times.
>
> It would make the logic much easier and faster -- since you could build
> a compound index on StartTime and EndTime fields.
>
> Sean
>

Thanks Sean. I have trouble with this kind thing sometimes.

--

Warm Regards,

Lee