Finding a date closest to a specific date

[Explorer5]

I have a table which has a bunch of dates, from 2003, all the way until 2006.
I'm trying to figure out an SQL statement which will allow me to find a date in
the table which is closest to a specific date.

For example, i want to find a date which is closest to todays date, whether
the date is in the past or the future doesnt matter, as long as its the closest
to today...

Anyone have any ideas?

[The ScareCrow]

maybe something like:

Select Min(myColumn) As theDate
From myTable
Where myColumn > #CreateODBCDate(Now())#
Or myColumn < #CreateODBCDate(Now())#

Ken

[cjeris]

At least on MS SQL Server, you can do it like this:

SELECT TOP 1 [id], [date]
FROM [my_table]
ORDER BY ABS(DATEDIFF(s, GETDATE(), [date])) ASC

DATEDIFF(s, GETDATE(), [date]) is the signed difference in seconds between the
[date] column and the current server date-time GETDATE(). ABS(...) computes
the absolute value because you don't care whether [date] is before or after
now. Then you pick the single first (smallest difference) row.

I do not know whether this query calls GETDATE() once for every row or every
comparison. If it does, you should probably move the call out of the ORDER BY
clause:

DECLARE @the_now DATETIME;
SET @the_now = GETDATE();
SELECT TOP 1 [id], [date]
FROM [my_table]
ORDER BY ABS(DATEDIFF(s, @the_now, [date])) ASC