CODEWITHSUNDEEP
sqlsql-server
gritts
gritts

Reputation: 185

SQL query not returning expected date time range

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.

Upvotes: 0

Views: 651

Answers (2)

Mattgb
Mattgb

Reputation: 428

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 :

Upvotes: 1

gritts
gritts

Reputation: 185

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

Upvotes: 0

Related Questions