Link to home
Start Free TrialLog in
Avatar of W D
W DFlag for United States of America

asked on

Comparison time portion of date/time field

Hi,
I need to compare the time portion of a date time field so that I create identify which employee shift it falls into. I have the following SQL:

SELECT
lo.order_proc_id
,CASE
WHEN To_Date(to_char(lo.end_procedure_time,'hh24:mi')) >= '08:00' AND
To_Date(to_char(lo.end_procedure_time,'hh24:mi')) <= '16:30' THEN '8to4'
WHEN To_Date(to_char(lo.end_procedure_time,'hh24:mi')) > '16:30' AND
To_Date(to_char(lo.end_procedure_time,'hh24:mi')) <= '24:00' THEN '4-30toMidnight'
WHEN To_Date(to_char(lo.end_procedure_time,'hh24:mi')) > '24:00' AND
To_Date(to_char(lo.end_procedure_time,'hh24:mi')) <= '08:00' THEN 'MidnightTo8'
ELSE NULL
END AS shift_time
FROM lab_orders lo

I get an error message stating "Not a valid month". How do I fix this?
Many thanks in advance for your review!
Avatar of W D
W D
Flag of United States of America image

ASKER

Actually, if I removed the To_Date and left to_char on each statement, would that work?
Avatar of W D
W D
Flag of United States of America image

ASKER

Or, would one not want to use to_char because applying comparison operands on strings may give incorrect results?
SOLUTION
Avatar of gatorvip
gatorvip
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of W D
W D
Flag of United States of America image

ASKER

I like both of these - just tried them and they both work.
Many thanks for your prompt replies!