sqlsql-serverdate

SQL server: Get record with date closest to given date


I have a table dbo.studies with datetime column studydate

I want to query the database using the datetime variable givendate to find the record closest to the datetime in column studydate

Using:

SELECT TOP 1 *
FROM studies
WHERE studies.studydate < givendate
ORDER BY studies.studydate DESC

Will result in the record that is less and closest to givendate, but I need the record closest to givendate, regardless of whether it's less or more then studydate

Any thoughts on how to find it?


Solution

  • One method is:

    SELECT TOP 1 s.*
    FROM studies s
    ORDER BY ABS(DATEDIFF(day, s.studydate, @givendate));
    

    This uses DATEDIFF() to get the closest date. Note that this is using day for the difference. If your "dates" have a time component, you might want a different date part.

    Note that this will not take advantage of indexes. A faster method (if you have the indexes) is a bit more complicated:

    SELECT TOP (1) s.*
    FROM ((SELECT TOP 1 s.*
           FROM studies s
           WHERE s.studydate <= @givendate
           ORDER BY s.studydate DESC
          ) UNION ALL
          (SELECT TOP 1 s.*
           FROM studies s
           WHERE s.studydate > @givendate
           ORDER BY s.studydate ASC
          )
         ) s
    ORDER BY DATEDIFF(day, s.studydate, @givendate);
    

    Although this is more complicated, each subquery can use an index on studydate. The final sort would have only two rows, so it should be really fast.