December 10, 2012 at 11:51 am
I have a select statement where I am doing some basic math with a datediff function. I have looked at the raw data and no where do I get a zero value when I subtract the two dates. But as soon as I do the division, I get the error. Originally I thought it had to do with the column I was dividing into, so I changed the numerator to 1.0 and I still get the same results. I think some where along the line SQL server is doing a conversion or rounding of my value which is where I am getting the zero in the denominator. I have tried both convert and cast to see if that was an issue. I have tried rounding my values before any division and I still get the same result. Any insight would be greatly appreciated.
This is my original statement.
ISNULL(round((hs.fractionalPart*(dr.LostGeneration/ ( DATEDIFF(SECOND,dr.startTime,dr.endTime) / 3600.0))),1),0.0)
I have broken down the above statement to cast the dateDiff to a float and the dr.LostGeneration column to a float and I still get an error.
Here is sample of the data used to perform the above statement. I think my issue is with the last 3 rows getting rounded.
select convert(float,DATEDIFF(SECOND,dr.startTime,dr.endTime))/3600.0, dr.startTime, dr.endTime
Calculated Col StartTime EndTime
0.1111111111111112012-12-10 10:42:45.0002012-12-10 10:49:25.000
0.1202777777777782012-12-10 12:17:06.0002012-12-10 12:24:19.000
0.11 2012-12-10 08:28:12.0002012-12-10 08:34:48.000
0.1113888888888892012-12-10 15:13:20.0002012-12-10 15:20:01.000
0.03527777777777782012-12-10 13:11:24.0002012-12-10 13:13:31.000
0.03416666666666672012-12-10 13:02:49.0002012-12-10 13:04:52.000
0.06555555555555562012-12-10 13:04:11.0002012-12-10 13:08:07.000
December 10, 2012 at 12:07 pm
try using a CASE statement to filter out the rows that have a zero denominator
something like this is my first guess:
CASE
WHEN DATEDIFF(SECOND,dr.startTime,dr.endTime) = 0
THEN 0
ELSE ISNULL(round((hs.fractionalPart*(dr.LostGeneration/ ( DATEDIFF(SECOND,dr.startTime,dr.endTime) / 3600.0))),1),0.0)
END
Lowell
December 10, 2012 at 12:17 pm
That did work. Thanks.
What I don't fully understand is if I look at the raw data, I don't see any values of zero after the dateDiff and if I also do a select * where DateDiff = 0 I get no rows returned.
December 10, 2012 at 12:52 pm
I can make a guess as to why you have a zero in the denominator. You are dividing an INT by 3600.0, and then this number is the denominator for a division using dr.LostGeneration, whose data type is the one that determines the data type for the implicit conversion. I tried the following query to see what happens if that data type is INT:
DECLARE @lg AS INT
SET @lg = 1
;WITH TALLY AS (
SELECT ROW_NUMBER() OVER(ORDER BY MSV.number) AS N
FROM master.dbo.spt_values AS MSV
CROSS APPLY master.dbo.spt_values AS SV2
WHERE MSV.number < 2250
AND MSV.number > 0
)
SELECT N
FROM TALLY
WHERE N < 3601
AND @lg / N / 3600.0 = 0
The results of this query include ALL the numbers from 1 to 3600. You might recognize the CTE as a TALLY table. I would suggest a slight re-ordering of how you compute this result, as follows:
ROUND((hs.fractionalPart * (CAST(dr.LostGeneration AS float)/
(ISNULL(NULLIF(DATEDIFF(SECOND,dr.startTime,dr.endTime),0),1.)/3600.))),1)
Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 10, 2012 at 12:59 pm
FYI, had to edit my last post (before this one) twice - please be sure to check it again...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 10, 2012 at 1:22 pm
Both posts work. Thanks.
December 10, 2012 at 11:08 pm
What about Hs.FeactionalPart? Is it a float or did you cast it as a float when you cast the other parts? SQL Server doesn't always do implicit conversions the way we expect. You can dig into the execution plan to see where the implicit conversion is happening as well.
Mickey Stuewe
Sr Database Developer
My blog
Follow me on twitter: @SQLMickey
Connect with me on LinkedIn
--------------------------------------------------------------------------
I laugh loudly and I laugh often. Just ask anyone who knows me.
December 11, 2012 at 2:12 am
try rearranging this
jkury (12/10/2012)
ISNULL(round((hs.fractionalPart*(dr.LostGeneration/ ( DATEDIFF(SECOND,dr.startTime,dr.endTime) / 3600.0))),1),0.0)
to
ISNULL(round((hs.fractionalPart*dr.LostGeneration*3600/ ( DATEDIFF(SECOND,dr.startTime,dr.endTime))),1),0.0)
If something odd is going on with implicit type conversions in the division this would prevent the problem with time diffs of under an hour
December 11, 2012 at 7:35 am
Grasshopper - I can't flip the order as you suggested because I get the difference in seconds and then convert that to minutes. We report to the minute, but want seconds for accuracy.
I also ensured that all values are of the same data type. I tested by manually converting all values to float.
December 12, 2012 at 4:39 am
You are dividing the denominator by 3600 - that is exactly the same mathematically as multiplying the numerator. However due to the computer handling floats using a fixed number of bits the division will result in a loss of precision (accuracy should not be affected at all unless there is a problem with your system's clock) compared to the multiplication. Assuming of course you aren't using large enough values to cause an overflow.
In practice for the example given I doubt you will notice the difference.
Note also by dividing by 3600 you convert from seconds to hours not minutes. My rearrangement does not change this.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply