Get the current effective date from the list of records has past and future dates in SQL Server

Question

I'm having list of records with a column EffectiveOn in SQL Server database table. I want to fetch the currently applicable EffectiveOn respective to current date. Consider the following table

Id         Data        EffectiveOn
_____________________________________
1          abc         2020-04-28
2          xyz         2020-08-05
3          dhd         2020-10-30
4          ert         2020-12-28
5          lkj         2021-03-19

In the above table I have to fetch the record (Id: 3) because the current date (i.e., today) is 2020-11-19

Expected Resultset

Id         Data        EffectiveOn
_____________________________________
3          dhd         2020-10-30

I tried the following solution but I can't How do I get the current records based on it's Effective Date?

Kindly assist me how to get the expected result-set.

Answer 1

You can do:

select top (1) *
from mytable t
where effectiveon <= convert(date, getdate())
order by effectiveon desc

This selects the greatest date before today (or today, if available).

Answer 2

You can try using Row_number function

select id, data, effectiveon 
( 
    select ROW_NUMBER()over(order by effectiveon desc )sno,* from #table 
    where effectiveon < cast(getdate() as date)
)a where sno=1