Find closest 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?

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

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)

I have a better solution for this problem i think.

I will show a few images to support and explain the final solution.

Background In my solution I have a table of FX Rates. These represent market rates for different currencies. However, our service provider has had a problem with the rate feed and as such some rates have zero values. I want to fill the missing data with rates for that same currency that as closest in time to the missing rate. Basically I want to get the RateId for the nearest non zero rate which I will then substitute. (This is not shown here in my example.)

1) So to start off lets identify the missing rates information:

Query showing my missing rates i.e. have a rate value of zero

2) Next lets identify rates that are not missing. Query showing rates that are not missing

3) This query is where the magic happens. I have made an assumption here which can be removed but was added to improve the efficiency/performance of the query. The assumption on line 26 is that I expect to find a substitute transaction on the same day as that of the missing / zero transaction. The magic happens is line 23: The Row_Number function adds an auto number starting at 1 for the shortest time difference between the missing and non missing transaction. The next closest transaction has a rownum of 2 etc.

Please note that in line 25 I must join the currencies so that I do not mismatch the currency types. That is I don't want to substitute a AUD currency with CHF values. I want the closest matching currencies.

Combining the two data sets with a row_number to identify nearest transaction

4) Finally, lets get data where the RowNum is 1 The final query

The query full query is as follows;

    ; with cte_zero_rates as
(
        Select      * 
        from        fxrates
        where       (spot_exp = 0 or spot_exp = 0) 
),
cte_non_zero_rates as
(
        Select      * 
        from        fxrates
        where       (spot_exp > 0 and spot_exp > 0) 
)
,cte_Nearest_Transaction as
(
        select       z.FXRatesID    as Zero_FXRatesID
                    ,z.importDate   as Zero_importDate
                    ,z.currency     as Zero_Currency
                    ,nz.currency    as NonZero_Currency
                    ,nz.FXRatesID   as NonZero_FXRatesID
                    ,nz.spot_imp
                    ,nz.importDate  as NonZero_importDate
                    ,DATEDIFF(ss, z.importDate, nz.importDate) as TimeDifferece
                    ,ROW_NUMBER() Over(partition by z.FXRatesID order by abs(DATEDIFF(ss, z.importDate, nz.importDate)) asc) as RowNum
        from        cte_zero_rates z 
        left join   cte_non_zero_rates nz on nz.currency = z.currency
                    and cast(nz.importDate as date) = cast(z.importDate as date)
        --order by  z.currency desc, z.importDate desc
)
select           n.Zero_FXRatesID
                ,n.Zero_Currency
                ,n.Zero_importDate
                ,n.NonZero_importDate
                ,DATEDIFF(s, n.NonZero_importDate,n.Zero_importDate) as Delay_In_Seconds
                ,n.NonZero_Currency
                ,n.NonZero_FXRatesID
 from           cte_Nearest_Transaction n
 where          n.RowNum = 1
                and n.NonZero_FXRatesID is not null
 order by       n.Zero_Currency, n.NonZero_importDate