CODEWITHSUNDEEP
sqloracle-databaseplsql
Nawaf
Nawaf

Reputation: 540

Case statement based on the time

I have the following table

enter image description here

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"

Upvotes: 0

Views: 80

Answers (2)

Nawaf
Nawaf

Reputation: 540

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

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.

Upvotes: 2

Related Questions