NOTE: This article has been updated and moved to the Codeless Platforms Documentation Portal.
If you are seeing this message and have not been redirected, click: How to Include a Date Range in an SQL Statement.
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.
Microsoft SQL Server
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