Find closest date in SQL Server

date, sql, sql-server

Solution

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

Problem

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?

Original source