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.37.~ [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.37.~ [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