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) You typically use the Database Query (ODBC) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. or Database Query (OLEDB) You typically use the Database Query (OLEDB) tool to extract specific records from a relational database, using a valid ODBC driver to make the connection. These are then made available to other task steps as a recordset. 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.