Introduction #
This article describes how to use the DATEDIFF() and GETDATE() functions in an SQL statement to extract records from a date range.
About the DATEDIFF() Function #
This function returns the difference between two dates. You can request the difference in years, quarters, months, days, weeks, hours, minutes, seconds, and so on. Use the format:
DATEDIFF(interval, date1, date2)
About the GETDATE() Function #
This function returns the current database system’s date and time, formatted as YYYY-MM-DD hh:mm:ss.mmm.
Creating the SQL Statement #
Use either the Database Query (ODBC) or Database Query (OLEDB) tool to create a simple select statement for the relevant table.
At the time of writing, the DATEDIFF() and GETDATE() functions cannot be configured using the user interface. Instead, click 
to change in free-type mode then manually enter the required syntax.
Example SQL to Return Records from the Last Seven Days #
Scenario: Return orders from the last seven days
SELECT
OrderID,
OrderDate
FROM
Orders
WHERE
DateDiff(day,[OrderDate],GetDate()) < = 7 AND DateDiff(day,[OrderDate],GetDate()) > 0
This function compares the OrderDate to today’s date and if the difference is less than or equal to seven days (and greater than zero), writes the OrderID and OrderDate to the recordset.
Example SQL to Return Records with Future Dates #
Scenario: Return orders with delivery dates in the next seven days
SELECT
OrderID,
DeliveryDate
FROM
Orders
WHERE
DateDiff(day,GetDate(),[DeliveryDate]) < = 7 AND DateDiff(day,GetDate(),[DeliveryDate]) > 0
This function compares today’s date to the DeliveryDate and if the difference is less than or equal to seven days (and greater than zero), writes the OrderID and DeliveryDate to the recordset. Note the position of the GetDate() function in this example — because we are looking for future dates, we declare the earliest date (today) first.
