1. 데이터 타입이 date 인가요?  네. date 타입입니다.
- 시간만 체크하는 게 맞는지?  네. 시간만 체크합니다.
- 일자까지 체크해야 하는 건 아닌지? 당일 시간만 체크해서 일자는 체크 안해도 될것 같습니다.
2. FROM 절엔 왜? dual 이 오죠?
- sta_date 가 테이블의 컬럼이 아닌가요?  네. 테이블 컬럼이 아닌 예시로 시작/종료 시간 표시했습니다.

답변이 이해가 안가네요.
- 시간만 체크하는데 왜? date 를 사용하는지?
- 테이블 컬럼이 아닌 예시라는게 도대체 뭔지?
위 질문 쿼리는 실행 가능한 코드가 아닌 것으로 보입니다.
입력 변수라고 한다면 문자형식을 이용하시면 될 것 같습니다.

SELECT CASE WHEN TO_CHAR(sysdate, 'hh24:mi') BETWEEN :sta_time AND :end_time
            THEN 'Y' ELSE 'N' END AS target
  FROM dual
;