Pfthb

I am working on a query that I hope to be able to use to query against a database for a specific range of time on a specific date. If I query for a full day of data I get the correct data returned. One row per hour of data available (0 - 23).

WHERE Documents.CreationTime BETWEEN '2014-10-01 00:00:00.000' AND '2014-10-01 23:59:59.999'

If I attempt to query for a portion of the day, the results are unusual.

WHERE Documents.CreationTime BETWEEN '2014-10-01 00:00:00.000' AND '2014-10-01 06:00:00.000'

Part day query returns: (Note the hours jump from 0 to 19)

 Hours Faxes Good Page Count
 0 3 4
 19 15 58
 20 4 9
 21 8 42
 22 2 4
 23 4 12

Here is my reduced query I created to try and resolve the issue.

SELECT DATEPART(hour, DATEADD(HH, - DATEDIFF(Hour, GETDATE(), GETUTCDATE()), Documents.CreationTime)) AS Hours
 ,COUNT(*) AS Faxes
 ,SUM(goodpagecount) AS [Good Page Count]
FROM Documents
JOIN Users
 ON Documents.OwnerID = Users.handle
JOIN Groups
 ON Users.GroupID = Groups.handle
JOIN History
 ON History.OWNER = Documents.handle
JOIN HistoryTRX
 ON History.handle = HistoryTRX.handle
WHERE Documents.CreationTime BETWEEN '2014-10-01 00:00:00.000'
 AND '2014-10-01 06:00:00.000'
GROUP BY DATEPART(hour, DATEADD(HH, - DATEDIFF(Hour, GETDATE(), GETUTCDATE()), Documents.CreationTime))
ORDER BY DATEPART(hour, DATEADD(HH, - DATEDIFF(Hour, GETDATE(), GETUTCDATE()), Documents.CreationTime))

Any suggestions as to what I am missing or improvements?

EDIT- More details
The "Documents.CreationTime" is in UTC. I am looking to have the "Hours" column correspond to local time. In this case UTC -5 as of this entry.

How about using the DATEADD function in your where clause:

WHERE Documents.CreationTime >= '20141001' AND Documents.CreationTime <= DATEADD(HOUR,6,'20141001')

Interesting Blog on the comment made by Lamak written by Aaron Bertrand :

• What do BETWEEN and the devil have in common?

Based on suggestions provided in response to my question, I came up with the following new query:

SELECT DATEPART(hour, DATEADD(HH,-DATEDIFF(Hour,GETDATE(),GETUTCDATE()),Documents.CreationTime)) AS Hours ,COUNT(*) AS Faxes,SUM(goodpagecount) AS [Good Page Count]
FROM Documents
JOIN Users ON Documents.OwnerID=Users.handle
JOIN Groups ON Users.GroupID=Groups.handle
JOIN History ON History.Owner=Documents.handle
JOIN HistoryTRX ON History.handle=HistoryTRX.handle
WHERE DATEADD(HH,-DATEDIFF(Hour,GETDATE(),GETUTCDATE()),Documents.CreationTime) >= '2014-10-01 00:00:00.000' and DATEADD(HH,-DATEDIFF(Hour,GETDATE(),GETUTCDATE()),Documents.CreationTime) <= '2014-10-03 08:00:00.000'
GROUP BY DATEPART(hour, DATEADD(HH,-DATEDIFF(Hour,GETDATE(),GETUTCDATE()),Documents.CreationTime))
ORDER BY DATEPART(hour, DATEADD(HH,-DATEDIFF(Hour,GETDATE(),GETUTCDATE()),Documents.CreationTime))

My changes are to the "WHERE" statement by adding my UTC compensation. The "WHERE" now matches the "SELECT".

Before:

WHERE Documents.CreationTime >= '2014-10-01 00:00:00.000' and Documents.CreationTime <= '2014-10-03 08:00:00.000'

After:

WHERE DATEADD(HH,-DATEDIFF(Hour,GETDATE(),GETUTCDATE()),Documents.CreationTime) >= '2014-10-01 00:00:00.000' and DATEADD(HH,-DATEDIFF(Hour,GETDATE(),GETUTCDATE()),Documents.CreationTime) <= '2014-10-03 08:00:00.000'

Also removed the BETWEEN keyword as it may not be as precise for results as I would like.

The results now look like:

 Hours Faxes Good Page Count
 0 3 4
 1 5 9
 3 9 50
 4 8 16
 5 14 40