Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table demo as
select arng_id,st_dt,end_dt
from
(
select 'abc' arng_id, date'2020-08-05' st_dt,date'2020-08-09' end_dt
from dual 
union all
select 'xyz',date'2020-08-10',date'2020-08-13' 
from dual 
union all
select 'abc1',date'2020-08-14',date'2020-08-20' 
from dual 
union all
select 'abcd',date'2020-08-14',date'2020-08-23' 
from dual 
union all
select 'abce',date'2020-08-24',date'2020-08-25' 
from dual 
)
5 rows affected

select demo.* from demo


ARNG_ID
ST_DT
END_DT


abc
05-AUG-20
09-AUG-20

xyz
10-AUG-20
13-AUG-20

abc1
14-AUG-20
20-AUG-20

abcd
14-AUG-20
23-AUG-20

abce
24-AUG-20
25-AUG-20


with data
as (select date'2020-08-26' input from dual),
max_data
as
(select max(end_dt) max_dt
  from demo
)  
select demo.*
  from demo,data,max_data
where (input between st_dt and end_dt) or
      (max_dt between end_dt and input)
order by end_dt desc
fetch first 1 row only


ARNG_ID
ST_DT
END_DT


abce
24-AUG-20
25-AUG-20


with data
as (select date'2020-08-14' input from dual)
select *
from
(select input,demo.*,max(end_dt) over (order by null) max_dt
  from demo,data)
where (input between st_dt and end_dt) or
      (max_dt between end_dt and input)
order by end_dt desc
fetch first 1 row only


INPUT
ARNG_ID
ST_DT
END_DT
MAX_DT


14-AUG-20
abcd
14-AUG-20
23-AUG-20
25-AUG-20

ARNG_ID	ST_DT	END_DT
abc	05-AUG-20	09-AUG-20
xyz	10-AUG-20	13-AUG-20
abc1	14-AUG-20	20-AUG-20
abcd	14-AUG-20	23-AUG-20
abce	24-AUG-20	25-AUG-20