Wednesday, March 7, 2012

rounding numerics


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:

select @.test * convert (float,1) - @.test2 as floater

-- floater
-- --
-- 9.9999999989999999

|||{ Obviously, I am in agreement with Geert. :-) }

No comments:

Post a Comment