How to find if a specific date is between two different row dates in SQL Server?

Try using self join.The query can be like this:-

select t1.columnname,......,from tablename t1 inner join tablename t2 on t1.id=t2.id where t1.endate>=t1.Planterminationdate and t1.enddate<=t2.Planeffectivedate

In the place of t1.columnname write the columnnames you need and change the tablename with the name of the table.

Which DB are you on? Also, are you always going to have 2 rows per ID? If yes, you could just do something like this -

select sub_qry.id, <other columns you need>,
case when end_date between min_pln_term and max_pln_eff then 'Yes' else 'No' end as your_check_flag
from (
select id,
min(PlanTerminationDate) as min_pln_term,
max(PlanEffectiveDate) as max_pln_eff,
EndDate as end_date
from your_table
group by id, EndDate ) sub_qry

If not, you'll have to try to use LEAD to get the subsequent start date for each termination date and then do a check on top of it.

select id, 
PlanEffectiveDate, 
PlanTerminationDate, 
lead(PlanEffectiveDate, 1) over (partition by id order by PlanEffectiveDate) as next_plan_eff_date,
EndDate
from your_table

Using the above as a sub query, you could just do -

select id, PlanEffectiveDate, PlanTerminationDate, next_plan_eff_date, end_date,
case when end_date between PlanTerminationDate and next_plan_eff_date then 'Yes' else 'No' end as your_check_flag
from above_sub_query

Another thing not clear is if you're always going to have one EndDate per ID. Assuming it is, the above query should work.