Posted by Erland Sommarskog on 06/16/05 01:09

Bruno Panetta (bpanetta@gmail.com) writes:
> How is it possible to avoid truncation errors in MS SQL? For example,
> if I run the following
>
> declare @a as decimal(38,8)
> declare @b as decimal(38,8)
> declare @c as decimal(38,8)
> set @a = 30.0
> set @b = 350.0
> set @c = @a/@b
> select @c
> set @c = @c*@b
> select @c
>
>
> I get 29.99990000 instead of 30.0. Is there a way around this?

30/350 is not a number that can be represented exactly as a decimal
number, @c will an approximation of the rational number, and thus you
will not be able to get back the original number in the general
case.

When I changed precision and scale to (28,10), the final result was without
rounding error, but that's chance and luck only.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]