Find date closest to today

[Guest]

I want to be able to query a date field with the result being the record
closest to today's date. Is there a way to do this simply?

Thanks,

Kim

 

[Allen Browne]

The nearest date is the one where the absolute value of the date minus today
is least, so:
ORDER BY Abs([Date1] - Date())

 

[John W. Vinson]

I want to be able to query a date field with the result being the record
closest to today's date. Is there a way to do this simply?

Thanks,

Kim

SELECT TOP 1 <whatever>
FROM yourtable
ORDER BY Abs(DateDiff("d", [datefield], Date())) DESC;

DateDiff will calculate the (positive or negative) number of days between the
datefield and today's date; Abs() will convert the negative to positive, so
that three days ago is seen as closer than four days from now; the ORDER BY
will sort them, closest first; and TOP 1 will display the closest record.

Note that if there are two or more tied records you'll see all the ties.

John W. Vinson [MVP]

 

[Marshall Barton]

I want to be able to query a date field with the result being the record
closest to today's date. Is there a way to do this simply?

SELECT TOP 1 *
FROM table
WHERE datefield < Date()
ORDER BY datefield DESC

 

[Guest]

Thanks everyone!

Kim

SELECT TOP 1 *
FROM table
WHERE datefield < Date()
ORDER BY datefield DESC