Re: Time Between Times

"b" <b_at_j.com> wrote in message news:<Drx99.159502$SS.6720102_at_bin3.nnrp.aus1.giganews.com>...
> Hello
>
> I have table with three columns shiftName (varchar), shiftStart (date),
> shiftEnd (date)
>
> The data is
>
> Shift 1, '1-Jan-1899 7:00:00 AM', '1-Jan-1899 3:00:00 PM'
> Shift 2, '1-Jan-1899 3:00:00 PM', '1-Jan-1899 11:00:00 PM'
> Shift 3, '1-Jan-1899 11:00:00 PM', '2-Jan-1899 7:00:00 AM'
>
> I picked 1-Jan-1899 because the date is of no use to me. I actually just
> need the Time.
>
> My task is to compare the current time [to_char(sysdate,'HH24:MI:SS')] and
> get back what shift I am in. I am fine with Shift 1, and Shift 2. That is
> simple. My problem comes with the query for Shift 3 the rolling over
> midnight has me stumped.
>
> I guess my question is how can I compare the current time to preset times
> and get back my shiftName column? Is there a between function?
>
> Thank you in Advance...
>
> Jack
Add a fouth line to your table:

Shift 1, '1-Jan-1899 7:00:00 AM', '1-Jan-1899 3:00:00 PM'
Shift 2, '1-Jan-1899 3:00:00 PM', '1-Jan-1899 11:00:00 PM'
Shift 3, '1-Jan-1899 11:00:00 PM', '2-Jan-1899 0:00:00 AM'
Shift 3, '1-Jan-1899 0:00:00 AM', '1-Jan-1899 7:00:00 AM'

You now have two entries to shift3: one "after 11pm" (for the first part) and another one "before 7am" (for the last part)

Your curreznt select procedure should continue to work. Received on Tue Aug 27 2002 - 16:15:19 CEST