Why data truncated when doing calculation 1

[immas]

When I execute the following lines the data is getting trucated after 6 decimal or 9 decimal, why? how to overcome this problem.

declare @a numeric(30,16), @b numeric(30,16)

set @a=12.1234567890123456
set @b=1.1

select @a
select @a*@b
select (@a*1.00)*(@b*1.00)
select (@a*@b)*1.00
select convert(float,@a*@b)


I am using SQL Server 2000.

Thanks
Immanuel

 

[zhavic]

I thing this is because calculations needs always double precision, so if you has 16 decimal digits, the right calculations
can be made only with numbers up to 8 decimal digits.

From your example 12.1234567890123456 * 1.1 = 13.33580246791358016
The result has 17 decimal digits, so it must be truncated, but the calculation was made with precision 16, so only the first 8 decimal digits can has the right values, the next 8 digits will be remainder.

If you change precission to numeric(38,32), you get the right results, but it takes longer time.

If you want to calculate values like that, with big precission, you need to use external program to do that.

If you want to know something more about that calculations, there is mathematical forum, I thing that can explain it more.
forum1229

Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.