Given a date, how to get next and previous date in database

Question

I have a database with a bunch of dates. I would like to, given a date, get the next date in the database and previous date in the database.

So given a databse with 10/10/09, 10/12/09, and 10/15/09, if someone enters the date 10/13/09, it should return 10/12/09 as the previous date and 10/15/09 as the next date.

How can I do this? Thanks!

Answer 1

You can do this easily using two queries:

-- get previous date
SELECT MAX(DateField) WHERE DateField < '2009-10-13';
-- get next date
SELECT MIN(DateField) WHERE DateField > '2009-10-13';

If you have an index on DateField, then this query will also be very efficient.

Answer 2

You can also use this to easily return the entire record rather than just the date.

-- Get previous date row
SELECT TOP 1 OrderDate, ... FROM Table WHERE OrderDate < @MyDate ORDER BY OrderDate DESC
-- Get Next date row
SELECT TOP 1 OrderDate, ... FROM Table WHERE OrderDate > @MyDate ORDER BY OrderDate

Note that the previous orders by the date in descending order where as the next orders by ascending order.