SQL & PL/SQL

with t as
(
select to_date('10/04/2021','mm/dd/yyyy') effective_date, to_date('10/05/2021','mm/dd/yyyy') end_date from dual
union all
select to_date('10/04/2021','mm/dd/yyyy') effective_date, null end_date from dual
union all
select to_date('10/04/2021','mm/dd/yyyy') effective_date, to_date('10/06/2021','mm/dd/yyyy') end_date from dual
)
select * from t where effective_date = : p_effective_date
and end_date = :p_end_date

scenario 1:

1. When :p_effective_date is to_date('10/04/2021','mm/dd/yyyy')
2. When :p_end_date = to_date('10/05/2021','mm/dd/yyyy')

EFFECTIVE_DATE END_DATE
-------------- ---------
04-OCT-21 05-OCT-21

scenario 2:

1. When :p_effective_date is to_date('10/04/2021','mm/dd/yyyy')
2. When :p_end_date = null

EFFECTIVE_DATE END_DATE
-------------- ---------
04-OCT-21 05-OCT-21
04-OCT-21
04-OCT-21 06-OCT-21