SQL floating point precision limited to 6 digits

Question

I have the following set of calculations in excel that I want to use in a stored procedure.

Excel

CellA: 45/448.2 = 0.100401606425703
CellB: 1-CellA = 0.899598393574297
CellC: 1-CellB = 0.100401606425703
CellD: CellC * 448.2 = 45.000000000000000

In SQL, I do the following:

declare @a decimal(18,15) = 45/448.2  
declare @b decimal(18,15) = 1-@a
declare @c decimal(18,15) = 1-@b
declare @d decimal(18,15) = @c * 448.2

I also tried performing a single line calculation

declare @e decimal(18,15) = (1-(1-(45/448.2)))*448.2

when I return the values, SQL gives me the following:

@a: 0.100401000000000
@b: 0.899599000000000
@c: 0.100401000000000
@d: 44.999728200000000

@e: 44.999728200000000

I tried to adjust the precision of decimal places in SQL, but I do not change anything, it returns only the first 6 digits of the decimal fraction.

Does Excel optimize when running formulas?

Any ideas?

+5

Scampdoodle Apr 08 '13 at 15:51

1 answer

AakashM · Accepted Answer · 2013-04-08T16:09:55+0000

Even for your first line, it’s enough to show the problem:

declare @a decimal(18,15) = 45/448.2  
print @a

gives

---------------------------------------
0.100401000000000

This is due to data types. When you say

448.2

( ) decimal, ,

Transact-SQL , . , 12.345 5 3.

448.2 - decimal(4,3). 45 integer, decimal 10 0. ,

Operation     Result precision                        Result scale
e1 / e2       p1 - s1 + s2 + max(6, s1 + p2 + 1)      max(6, s1 + p2 + 1)

10 - 3 + 0 + max(6, 0 + 3 + 1) max(6, 0 + 3 + 1), 13 6.

6 , .

, , ; , :

, :

declare @a decimal(18,15) = 45/448.2e0  
select @a

---------------------------------------
0.100401606425703

:

declare @a decimal(18,15) = 45/cast(448.2 as decimal(18,10))
select @a

---------------------------------------
0.100401606425703