Try
select TOP 1 id, [Date]
from Avinash
order by Abs(Datediff(dd, getdate(), [Date])), [Date]
This will find the closest date regardless of whether it is before or after the current date due to the use of
Abs
If there are two dates equally close (i.e. x days before current date and x days after) then the query currently returns the earlier date. If you want it to return the future date then order by
Abs(Datediff(dd, getdate() Asc, [Date])), [Date] Desc