Hello,
Executing this statement outputs 10.0000000000. I expect it to be 9.999999999.
declare @.test NUMERIC(24,10)
declare @.test2 NUMERIC(24,10)
set @.test2 = 0.0000000000
set @.test = 9.999999999
select @.test * (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
Hi,
You need to change the script like this to avoid an implicit cast to an integer:
declare @.test NUMERIC(24,10)
declare @.test2 NUMERIC(24,10)
set @.test2 = 0.0000000000
set @.test = 9.999999999
select @.test * cast(1 - @.test2 as NUMERIC(24,10))
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||At least part of the answer lies with the fact that the "1" in the statement:
select @.test * (1 - @.test2)
is an integer datatype and must be converted for all of the operations to take place. This can be avoided by performing explicit converting to the numeric(24,10) datatype such as:
select @.test * convert (numeric(24,10), (convert (numeric(24, 10),1) - @.test2)) as converted
-- converted
-- -
-- 9.999999999
Also beware that converting to a float might not be exactly what you want either:
|||{ Obviously, I am in agreement with Geert. :-) }select @.test * convert (float,1) - @.test2 as floater
-- floater
-- --
-- 9.9999999989999999
No comments:
Post a Comment