In your answer Selecting the latest order for a customer (March 15 2002), you said:
What's different this time is that you want the closest date to a specified date, not the latest date. This might mean closest either before or after which would require a DATEDIFF function and an ABS value -- but I'll assume you want the closest before the specified date. This is simpler, and allows the use of MAX() with an additional WHERE condition:select * from Customers , Orders where Customers.ID = Orders.Cust_ID and Orders.OrderDate = ( select max(OrderDate) from Orders where Cust_ID = Customers.ID and OrderDate < somedate )
I actually do need to write a query to get the date that is closest to another date (closest either before or after). Can you provide more information on how the DATEDIFF and ABS functions will help, or even provide an example?
DATEDIFF and ABS are specific to Microsoft SQL Server and Access; other database systems will have similar functions. The basic strategy for this problem is to use a subquery which calculates the minimum absolute difference between the date value in the column and given date. The outer query then selects the row where the absolute difference is equal to the minimum.
select TheDate from TheTable where abs(datediff('d',TheDate,somedate)) = ( select min( abs(datediff('d',TheDate,somedate)) ) from TheTable )
Note that this will return all dates that are closest to the given date; there could be more than one date that is the same number of days away.
This was first published in October 2002