위의 데이터에는 분명 날자의 변화가 생기는데 날자를 관리 하지 않으면 뽑아 낼수 없습니다

물론 시작 시간과 종료 시간은 절대 23시간 59분 59초 이상 차이나지 않는다는 전제가

있다면 아래와 같이 시작시간과 종료 시간을 구할 수는 있습니다

WITH T(CODE, S_TM, E_TM) AS (

    SELECT 1, '08:30:00', '20:29:59' FROM DUAL UNION ALL

    SELECT 2, '20:30:00', '08:29:59' FROM DUAL

)

SELECT CODE, TO_CHAR(S_TM, 'HH24:MI:SS') S_TM, TO_CHAR(E_TM, 'HH24:MI:SS') E_TM

FROM (

    SELECT

        CODE,

        TO_DATE(S_TM, 'HH24:MI:SS') S_TM,

        CASE WHEN TO_DATE(S_TM, 'HH24:MI:SS') - TO_DATE(E_TM, 'HH24:MI:SS') > 0 THEN TO_DATE(E_TM, 'HH24:MI:SS') + 1

            ELSE TO_DATE(E_TM, 'HH24:MI:SS')

        END E_TM

    FROM T

)

WHERE TO_DATE('08:40:00', 'HH24:MI:SS') BETWEEN S_TM AND E_TM

하지만 질문 하신데로 데이터는 날자의 변화가 생기는데 00:40:30 과 같은 데이터만 가지고는

결과를 정확하게 뽑아 낼수 없습니다

D 일 00:40:40 초인지 D+1 일 00:40:40 초인지 명확하게 해주어야 데이터 추출이 가능합니다

조회하고자 하는 00시40분40초는 어느 날자 기준인가요?

테이블에 있는 값들에서 08:30:00을 빼면, CODE=1 은 오전(AM)에 해당하고 CODE=2 는 오후(PM)에 해당합니다.

들어온 날짜시간에서 8.5/24 를 빼서 그 결과가 AM/PM 중에 언제에 해당하는지를 찾으면 될것 같습니다.

WITH work_time AS
(
SELECT 1 code, '08:30:00' from_time, '20:29:59' to_time FROM dual
UNION ALL SELECT 2, '20:30:00', '08:29:59' FROM dual
)
SELECT *
  FROM (SELECT '00:40:40' v_time FROM dual)
     , work_time
 WHERE (from_time <= to_time AND v_time BETWEEN from_time  AND to_time   )
    OR (from_time >  to_time AND v_time BETWEEN from_time  AND '23:59:59')
    OR (from_time >  to_time AND v_time BETWEEN '00:00:00' AND to_time   )
;

부족한 저의 지식에 이렇게 열의를 가져주진점 너무 감사합니다.

여러분들의 도움으로 많은 부분이 해결되어 정말 감사합니다.