I need to compute the shift timings based on the below time range 6 AM - 1:59:59 PM as AM shift 2 PM - 9:59:59 PM as PM shift 10 PM - 5:59:59 AM as NTS shift else null
Can some one help me to do the below with decode statement in Oracle 19C?
SELECT
CASE WHEN to_char(wtc2.actual_end_datetime, 'hh24:mi:ss') BETWEEN '06:00:00' AND '13:59:59' THEN 'AM'
WHEN to_char(wtc2.actual_end_datetime, 'hh24:mi:ss') BETWEEN '14:00:00' AND '21:59:59' THEN 'PM'
WHEN to_char(wtc2.actual_end_datetime, 'hh24:mi:ss') > '21:59:59' OR
to_char(wtc2.actual_end_datetime, 'hh24:mi:ss') < '06:00:00' THEN 'NTS'
ELSE NULL
END dropoff_shift
FROM TAB1 wtc2
Thanks
Gautam