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