Last second of future date

The following query is useful when trying to select an inclusive range of records using DATETIME.

For this example, we will use GETDATE() which returns the current date & time. You can modify this at any instance in the code below to a variable (@date) or static date time (2012-08-05 15:10:44).

We want to use the BETWEEN operator to return the results where the begin date is greater than or equal to the test date, and where the end date is less than or equal to the test date.

Let’s prepare the begin date.

DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

This will return the DATETIME format of the beginning of GETDATE(). If run on August 2, 2012, it would return 2012-08-02 00:00:00.000.

DATEADD(s, -1, DATEADD(d, 1, DATEADD(d, DATEDIFF(d, 0, DATEADD(wk, 2, GETDATE())), 0)))

The end date (two weeks from GETDATE()) will return the last second of that day. If run on August 2, 2012, it would return 2012-08-16 23:59:59.000.

Putting it together:

SELECT * FROM myTable 
WHERE theDate BETWEEN DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND 
    DATEADD(s, -1, DATEADD(d, 1, DATEADD(d, DATEDIFF(d, 0, DATEADD(wk, 2, GETDATE())), 0)))