Case statement based on the time

I have the following table

Based on the transaction_DT: if the transaction between 04:00:00 PM to 08:00:00 AM + next day get “After Hour”

Between 08:00:00 AM to 04:00:00 PM within same day get “Working Hour”

Using the case statement it is not works!

CASE
          WHEN   (  Transacton_DT >= TO_DATE ('4:00:00 PM', 'HH:MI:SS PM')
               AND Transacton_DT  <= TO_DATE ('11:59:00 PM', 'HH:MI:SS PM') )
               OR ( Transacton_DT  >= TO_DATE ('12:01:00 AM', 'HH:MI:SS AM')
               AND Transacton_DT  <= TO_DATE('8:00:00 AM', 'HH:MI:SS AM') )
          THEN
             'After Hour'
 ELSE
             'Working Hour'
 END AS "Shift"

Answer

Hmmm . . . how about something like this:

(case when to_char(transaction_dt, 'HH24:MI') between '08:00' and '16:00'
      then 'Working hours' else 'After hours'
 end)

Your code doesn’t work because you are comparing a value with a time component only (well a default date component) to one with a date component.