Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: