PDA

View Full Version : Select nearest date



kunguito
09-03-2008, 03:36 AM
I have a single table Called MachineRevision.
The Fields of this table are:
Model: A reference code of the machine.
Revision: what was done to the machine (not relevant to the question)
Date: when was revised for the last time.


I need a SQL statement that retrieves which model was revised on the nearest date to a parameter date.

kunguito
09-03-2008, 06:05 AM
These Nested selects might do:


SELECT MachineRevision.Model
FROM MachineRevision
WHERE (((ModDate)=
(SELECT MAX(ModDate) FROM
(SELECT ModDate FROM MachineRevision WHERE ModDate <[UserInputDate]))));

Mavyak
09-03-2008, 10:23 AM
I think this will work but it is untested:

SELECT TOP 1 MachineRevision.Model
FROM MachineRevision
WHERE ModDate <= [UserInputDate]
ORDER by ModDate DESC