Closest date before or after a given date

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?


    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top