james_weasel james_weasel - 6 months ago 12
SQL Question

Parameters on time stamps in oracle sql developer

select TO_CHAR(date1,'HH24:MI:SS') time_in,
CASE
WHEN TO_CHAR(date1,'HH24:MI:SS') between '6:00:00' AND '18:00:00' then 'day_shift'
WHEN TO_CHAR(date1,'HH24:MI:SS') BETWEEN '18:00:00' AND '24:00:00' then 'night_shift'
WHEN TO_CHAR(date1,'HH24:MI:SS') BETWEEN '0:00:00' AND '6:00:00' then 'night_shift'
END AS shift
from blah


Trying to make it so you can see what shift it is based off a time stamp I am pulling from a table in sql developer. Any suggestions or help?

Answer

HH24 creates zero-padded hours. You might try:

select TO_CHAR(date1,'HH24:MI:SS') time_in,
       (CASE WHEN TO_CHAR(date1,'HH24:MI:SS') between '06:00:00' AND '18:00:00'
             THEN 'day_shift'
             WHEN TO_CHAR(date1,'HH24:MI:SS') BETWEEN '18:00:00' AND '24:00:00'           
             THEN 'night_shift' 
             WHEN TO_CHAR(date1,'HH24:MI:SS') BETWEEN '00:00:00' AND '06:00:00'          
             THEN 'night_shift'
        END) AS shift
from blah