Find closest date in SQL Server

Question

I have a table dbo.X with DateTime column Y which may have hundreds of records.

My Stored Procedure has parameter @CurrentDate, I want to find out the date in the column Y in above table dbo.X which is less than and closest to @CurrentDate.

How to find it?

Answer 1

The where clause will match all rows with date less than @CurrentDate and, since they are ordered descendantly, the TOP 1 will be the closest date to the current date.

SELECT TOP 1 *
FROM x
WHERE x.date < @CurrentDate
ORDER BY x.date DESC