How to write a date & time shift query?

Question

Can someone please help me with writing a CASE query. Using the column name PERFORM_DT_TM - date & time format "MM-DD-YYYY HH-MI-SS"

Table name PERFORM_RESULT

The result I want is something like this:

BETWEEN'06:00:00'AND'14:29:59' THEN 'First Shift'
BETWEEN'14:30:00'AND'22:59:59' THEN 'Second Shift'
BETWEEN '23:00:00' AND'05:59:59' THEN 'Third Shift'
ELSE 'UNKNOWN'

Answer 1

You can use to_char(PERFORM_RESULT, 'HH24:MI') to get the hours and minutes from the date; the seconds aren't needed for this.

For 23:00 to 05:59 you need to check two ranges, because BETWEEN doesn't know about clock wraparound, it's just doing a textual comparison.

SELECT CASE
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') BETWEEN '06:00' AND '14:29' THEN 'First Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') BETWEEN '14:30' AND '22:59' THEN 'Second Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') BETWEEN '23:00' AND '23:59' THEN 'Third Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') BETWEEN '00:00' AND '05:59' THEN 'Third Shift'
    ELSE 'UNKNOWN'
END

You can also take advantage of the fact that cases are tested sequentially to simplify it, since you don't have to check the beginning of a range if that has been excluded by the previous case.

SELECT CASE
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') <= '05:59' THEN 'Third Shift'    
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') <= '14:29' THEN 'First Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') <= '22:59' THEN 'Second Shift'
    WHEN to_char(PERFORM_DT_TM, 'HH24:MI') <= '23:59' THEN 'Third Shift'
    ELSE 'UNKNOWN'
END