CODEWITHSUNDEEP
sql-servert-sql
InteXX
InteXX

Reputation: 6367

What is the TSQL syntax for selecting rows BETWEEN DateTime values

I have data in 5-minute intervals with 24-hour roll-overs, like so:

LogTime
-------------
7/1/2017 7:01
7/1/2017 7:06
7/1/2017 7:11
7/1/2017 7:16
...
7/2/2017 6:56

I need a TSQL query to select all rows for a given date, e.g. entries from 07/02 00:00:00 through 07/02 06:59:59 are considered to be part of 07/01's data.

I'm using this query in SSMS' editor pane:

SELECT * FROM [LogEntries] WHERE [LogTime] BETWEEN '20170701 06:59:59' AND '20170702 07:00'

The problem here is that the strings need to be logically constructed from DateTime values.

Can this be done in a single query statement with passed-in DateTime parameters? I'm doing this for an SSRS report and I'd rather not resort to a Stored Procedure if I don't have to.

Upvotes: 4

Views: 131

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

The simplest solution (though not sargable, so it might not be the best) is simply to subtract 7 hours from your LogTime values:

DECLARE @DateTime DateTime = GETDATE();

SELECT * 
FROM [LogEntries] 
WHERE CAST(DATEADD(HOUR, -7, [LogTime]) AS DATE) = CAST(@DateTime AS DATE);

A sargable solution is a little more cumbersome, but still quite easy:

SELECT * 
FROM [LogEntries] 
                   -- 07 am on the date of the @DateTime variable
WHERE [LogTime] >= CAST(CAST(@DateTime AS DATE) As DateTime) + CAST('00:07:00' As DateTime) 
                 -- 07 am one day after the date of the @DateTime variable
AND  [LogTime] < DATEADD(DAY, 1, CAST(CAST(@DateTime AS DATE) As DateTime) + CAST('00:07:00' As DateTime))

Of course, if you pass a Date variable instead of a DateTime, it will save you one conversion (you can use + to add datetime values together, but not date + time, so you still must convert to datetime):

SELECT * 
FROM [LogEntries] 
                -- 7 AM
WHERE [LogTime] >= CAST(@Date As DateTime) + CAST('00:07:00' As DateTime)
                -- 7 AM on the next day
AND  [LogTime] <= DATEADD(DAY, 1, CAST(@Date As DateTime) + CAST('00:07:00' As DateTime))

Upvotes: 3

Richardissimo
Richardissimo

Reputation: 5763

The question states "The problem here is that the strings need to be logically constructed from DateTime values".

In the BETWEEN (Transact-sql) documentation cited by the OP, example D (for datetimes) says this:

D. Using BETWEEN with datetime values

The following example retrieves rows in which datetime values are between '20011212' and '20020105', inclusive.

SELECT BusinessEntityID, RateChangeDate
FROM HumanResources.EmployeePayHistory
WHERE RateChangeDate BETWEEN '20011212' AND '20020105';

This makes it seem that the DateTime values needs to be supplied as strings. They don't. You can use datetimes.

Zohar's answer addresses the time-shifting part of the problem.

Upvotes: 4

Related Questions