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 

Answer 2

Use DateDiff and order your result by how many days or seconds are between that date and what the Input was

Something like this

    select top 1 rowId, dateCol, datediff(second, @CurrentDate, dateCol) as SecondsBetweenDates     from myTable     where dateCol < @currentDate     order by datediff(second, @CurrentDate, dateCol)