[SOLVED] SQL server: Get record with date closest to given date

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.