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?
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.