Is there way to pull data for only the current shift on the current day in Oracle SQL?

Question

I am trying to pull data for whatever the current shift is from an oracle database, the two shifts are 0830-2030 and 2030-0830. I am currently using this below but I think there is a problem with the first line.

IF SYSDATE(HHMM) >= 0830 AND SYSDATE(HHMM) <= 2030
THEN
SELECT * FROM table
WHERE rej_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND TO_NUMBER(rej_TIME) > 830 AND TO_NUMBER(rej_TIME) < 2030;
ELSE
SELECT * FROM table
WHERE rej_date = TO_CHAR(SYSDATE -1,'YYYYMMDD') OR rej_date = TO_CHAR(SYSDATE,'YYYYMMDD')
AND TO_NUMBER(rej_TIME) > 2030 AND TO_NUMBER(rej_TIME) < 830;

I basically have a table with columns 'RollID','Reject_Type','rej_date(yyyymmdd char)','rej_time'(HHMM char) and there are entries throughought the day, I would only like to return the rows that fall under the current shift whether it be day shift 0830-2030 or the night shift 2030-0830, so I have to account for the day change as well. Ive tried taking the equals off the first line but it breaks and says unknown command.

Answer 1

If you do not have future dates/times then:

SELECT *
FROM   table_name
WHERE  TO_DATE(rej_date||rej_time, 'YYYYMMDDHH24MI')
         >= CASE
            WHEN EXTRACT(HOUR FROM SYSTIMESTAMP - INTERVAL '08:30' HOUR TO MINUTE) <= 12
            THEN TRUNC(SYSDATE - INTERVAL '08:30' HOUR TO MINUTE) + INTERVAL '08:30' HOUR TO MINUTE
            ELSE TRUNC(SYSDATE - INTERVAL '08:30' HOUR TO MINUTE) + INTERVAL '20:30' HOUR TO MINUTE
            END;

Which, for the sample data:

CREATE TABLE table_name (rej_date, rej_time) AS
SELECT TO_CHAR(dt, 'YYYYMMDD'),
       TO_CHAR(dt, 'HH24MI')
FROM   (
  SELECT TRUNC(SYSDATE - 1) + INTERVAL '30' MINUTE * (LEVEL - 1) AS dt
  FROM   DUAL
  CONNECT BY TRUNC(SYSDATE - 1) + INTERVAL '30' MINUTE * (LEVEL - 1) <= SYSDATE
)

Outputs the times within the current shift:

REJ_DATE	REJ_TIME
20240213	2030
20240213	2100
20240213	2130
20240213	2200

fiddle