Calculate time of the day & Shift in a day !!

SELECT TRUNC(SYSDATE) + 7 / 24
FROM DUAL;

-----------------------------------------------------------------------

SELECT SH.SHIFT_NAME, SH.START_TIME, SH.END_TIME
FROM SHIFTS SH
 WHERE (TO_DATE(TO_CHAR(ISSUE_TIME, 'hh:mi:ss am'), 'hh:mi:ss am') +
             (CASE WHEN TO_DATE(SH.END_TIME, 'hh:mi:ss am') <
               TO_DATE(SH.START_TIME, 'hh:mi:ss am')
                 THEN(CASE WHEN
                                      TO_DATE(TO_CHAR(ISSUE_TIME, 'hh:mi:ss am'), 'hh:mi:ss am') BETWEEN
                                      TO_DATE('00:00:00', 'hh24:mi:ss') AND
                                      TO_DATE('06:59:59 am', 'hh:mi:ss am') THEN 1 ELSE 0 END) ELSE 0 END)) BETWEEN
                               TO_DATE(SH.START_TIME, 'hh:mi:ss am') AND
                               (CASE WHEN TO_DATE(SH.END_TIME, 'hh:mi:ss am') <
                                TO_DATE(SH.START_TIME, 'hh:mi:ss am') THEN
                                TO_DATE(SH.END_TIME, 'hh:mi:ss am') + 1 ELSE
                                TO_DATE(SH.END_TIME, 'hh:mi:ss am') END)
                           AND SH.HIDE_FLAG = 'N'