CODEWITHSUNDEEP
sqlmysql
giroy
giroy

Reputation: 2243

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

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!

Upvotes: 2

Views: 8558

Answers (2)

Peter Oehlert
Peter Oehlert

Reputation: 16928

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.

Upvotes: 4

too much php
too much php

Reputation: 90978

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.

Upvotes: 8

Related Questions