Division Precision

[OmidLeo]

Hi All,

A very simple but confusing problem, I've now a lot of calculations in stored procedures that are all inaccurate!, e.g
1234567890 * (1.0 - 0.5/14.0)

the result is actually : 1,190,476,179.~
but SQL gives me: 1,190,476,532.3 7.~ [MSSQL 2005/SP2]


Is any way to force SQLServer to use maximum precision in constants and division results in a formula?

[deepuv04]

Hi All,

A very simple but confusing problem, I've now a lot of calculations in stored procedures that are all inaccurate!, e.g
1234567890 * (1.0 - 0.5/14.0)

the result is actually : 1,190,476,179.~
but SQL gives me: 1,190,476,532.3 7.~ [MSSQL 2005/SP2]


Is any way to force SQLServer to use maximum precision in constants and division results in a formula?

Hi,

you can achieve this in two ways

for example if you have the datatypes as follows
declare @a numeric(5,2)
declare @b numeric(5,2)
declare @c numeric(5,2)


set @a = 10.0
set @b = 5.0
set @c = @a/@b
select @a/@b,@c -- see the differencein the the output of two columns

another way is calculate the values first and convert to some round value
or limit the precision value
SELECT CONVERT(NUMERIC (12,2), 1234567890 * (1.0 - 0.5/14.0) )
SELECT 1234567890 * (1.0 - 0.5/14.0)


thanks