Time Between Times

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 Received on Fri Aug 23 2002 - 23:04:35 CEST