Database Query – Setting Date Criteria

Summary

The content of this knowledge base article will provide a solution for building an SQL statement that contains date criteria set on a date field of + (plus) or- (minus) a number of days. This will allow data to be displayed either side of a specified date.

It will use the DATEDIFF() function which returns the time between two dates and the GETDATE() function which returns the current date and time.

The syntax depends on the database server you are using.

Applies To

Microsoft SQL Server

Solution

The below example SQL statement shows how a query can return results for the past 7 days before a date or 7 days after.

Return orders for the last 7 days:

SELECT OrderID, OrderDate

FROM Orders

WHERE DateDiff(day,[OrderDate],GetDate()) < = 7 AND DateDiff(day,[OrderDate],GetDate()) > 0

Return delivery dates for the next 7 days

SELECT OrderID, DeliveryDate

FROM Orders

WHERE DateDiff(day,GetDate(),[DeliveryDate]) < = 7 AND DateDiff(day,GetDate(),[DeliveryDate]) > 0