Help! Different answers for same query
-
I have a Help Desk application that writes a UNIX timestamp to SQL Server. With the help of this forum I created a query that converts that timestamp into a format that SQL Server will understand for reporting purposes. I discover that if I do a query over a date range of a few months I get a total. But when I do the same query month by month and then add those together the results do not match.
I suspect there is a problem with my timestamp conversion but being a relatively new DBA I'm not sure.
Can someone shed some light on the problem? The query is as follows:SELECT COUNT(open_date) AS NumberOfRequests
FROM AHD.call_req
WHERE DATEADD(hh, -7, DATEADD(ss, open_date, '19700101')) BETWEEN '08-19-03' AND '12-23-03'
GO
Thanks in advance for any help you can provide.
-
I'm guessing that the problem is with your use of the BETWEEN operator. It's difficult to get correct results using BETWEEN with temporal values that contain times. For example, if your "month" queries use predicates like:
DATEADD(hh, -7, DATEADD(ss, open_date, '19700101')) BETWEEN '2003-01-01' AND '2003-01-31'
You will probably be missing most rows opened on the last day of the month, as they will have been created later than midnight. Try instead:
DATEADD(hh, -7, DATEADD(ss, open_date, '19700101')) >= '2003-01-01' AND DATEADD(hh, -7, DATEADD(ss, open_date, '19700101')) < '2003-02-01'
You can also use something like this, but I'm sure you see the inherent problems with it:
DATEADD(hh, -7, DATEADD(ss, open_date, '19700101')) BETWEEN '2003-01-01' AND '2003-01-31 23:59:59.997'
--Jonathan -
Jonathan
I changed the query and the results now match. Your comment regarding the last day of the month triggered a recollection about something similar I saw recently in SQL Server magazine. I'll have to do some research tonight. Thanks for pointing me in the right direction.
-Michael
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply