Round numerics
-
Hello,
Executing this statement outputs 10.0000000000. I expect it to be 9.999999999.
declare
@test-2 NUMERIC(24,10)
declare
@test2 NUMERIC(24,10)
set
@test2 = 0.0000000000
set
@test-2 = 9.999999999
select
@test-2 * (1 - @test2)
Changing the type of @test2 to VARCHAR(12,10) corrects the problem. I don't understand why SQL Server does this rounding?
Thanx,
Wouter
-
you have to understand how it is calculating the percision and scale for the result.
your numbers have a percision of 14 scale of 10, so the resulting percision is 14 + 14 + 1 +1, which = 30. The max percision + scale = 38, so 29 - 38 = 9, you result has a scale of 8. You get the +1 +1 to percision because you did 1-, then you multiplied (look at the chart below and notice your results has a percision of 8)
lowering your percision to 22 makes it work
According to books online
The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.
Operation Result precision Result scale * e1 + e2
max(s1, s2) + max(p1-s1, p2-s2) + 1
max(s1, s2)
e1 - e2
max(s1, s2) + max(p1-s1, p2-s2) + 1
max(s1, s2)
e1 * e2
p1 + p2 + 1
s1 + s2
e1 / e2
p1 - s1 + s2 + max(6, s1 + p2 + 1)
max(6, s1 + p2 + 1)
e1 { UNION | EXCEPT | INTERSECT } e2
max(s1, s2) + max(p1-s1, p2-s2)
max(s1, s2)
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.
See Also
See AlsoViewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply