Q

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.


This was first published in October 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

1 comment

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close