Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Can we convert this case statement to decode to calculate the shift timings in oracle sql?

msJan 26 2022

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

Comments

Post Details

Added on Jan 26 2022
#performance
8 comments
610 views
1 mention