sqlsql-serverdate

Find closest earlier date in SQL Server

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?

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