요건 테스트 안하고도 적을 수 있을 듯...^^;
select *
from t
where edt >= '20091115'
and sdt <= '20091121'

마농님 쿼리를 살짝 응용했습니다.

화면단에서 조회기간이 주어지면 그 기간동안 행사가 걸쳐있는 모든 날짜를 구하시오
EX)조회기간이 '20091115' ~ '20091121' 로 주어지면 행사3은 15,16,17일 3일이 걸쳐있습니다.

WITH t AS
(
SELECT '행사1' cd, '20091101' sdt, '20091107' edt FROM dual
UNION ALL SELECT '행사2', '20091109', '20091115' FROM dual
UNION ALL SELECT '행사3', '20091113', '20091117' FROM dual
UNION ALL SELECT '행사4', '20091115', '20091121' FROM dual
UNION ALL SELECT '행사5', '20091117', '20091118' FROM dual
UNION ALL SELECT '행사6', '20091120', '20091123' FROM dual
UNION ALL SELECT '행사7', '20091122', '20091130' FROM dual
UNION ALL SELECT '행사8', '20091101', '20091130' FROM dual
)
, COPY AS(
SELECT TO_DATE('20091115')+(LEVEL-1) BDATE
FROM DUAL
CONNECT BY LEVEL<=(TO_DATE('20091121')- TO_DATE('20091115')+1)
)
SELECT CD
,SDT
,EDT
,BDATE
FROM t A,COPY B
WHERE B.BDATE BETWEEN TO_DATE(SDT) AND TO_DATE(EDT)
ORDER BY CD,BDATE

WITH t AS
(
SELECT '행사1' cd, '20091101' sdt, '20091107' edt FROM dual
UNION ALL SELECT '행사2', '20091109', '20091115' FROM dual
UNION ALL SELECT '행사3', '20091113', '20091117' FROM dual
UNION ALL SELECT '행사4', '20091115', '20091121' FROM dual
UNION ALL SELECT '행사5', '20091117', '20091118' FROM dual
UNION ALL SELECT '행사6', '20091120', '20091123' FROM dual
UNION ALL SELECT '행사7', '20091122', '20091130' FROM dual
UNION ALL SELECT '행사8', '20091101', '20091130' FROM dual
)
SELECT CD,SDT,EDT
FROM t
WHERE '20091115' <= SDT
AND EDT <= '20091121'

;