Closest date before or after a given date

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.


Dig Deeper on Oracle development languages

Data Management
Business Analytics
SearchSAP
TheServerSide.com
Data Center
Content Management
HRSoftware
Close