SQL floating-point precision limited to 6 digits

Question

I have the following set of calculations in excel that I want to be able 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 am doing 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 have also tried running the calculation in one line

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've tried adjusting the precision of the decimals in SQL but I nothing makes a difference, it only returns the first 6 digits of the decimal.

Does Excel do any optimization when running the formula?

Any ideas?

Try, say, `SELECT 45/CAST(448.2 AS DECIMAL(18,1))`. [The links here](http://stackoverflow.com/a/5385417/73226) tell you the rules for the resultant datatype of division. — Martin Smith, Apr 08 '13 at 16:03
@MartinSmith that does give me a more complete decimal for @a, though my answer is 45.000000000000085 are you able to explain why? — ScampDoodle, Apr 08 '13 at 16:07
It's explained in the two links from the previous answer which I was too lazy to reproduce above. `e1 / e2 gives p=p1 - s1 + s2 + max(6, s1 + p2 + 1), s = max(6, s1 + p2 + 1)`. If `p` would be `> 38` then `s` is truncated at `6` — Martin Smith, Apr 08 '13 at 16:09

score 8 · Accepted Answer · answered Apr 08 '13 at 16:09

Even just your first line is enough to show the problem:

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

gives

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

This is because of data types. When you say

448.2

it is (per the documentation) interpreted as a constant of type decimal, and also per the documentation,

In Transact-SQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.

So 448.2 is decimal(4,3). 45 is integer, which when combined with a decimal is treated as having precision of 10 and scale 0 . When we divide, the rules say

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

which in this case gives a result precision of 10 - 3 + 0 + max(6, 0 + 3 + 1) and scale of max(6, 0 + 3 + 1), which comes out to 13 and 6.

That result scale of 6 is why the result only has those six decimal places.

The way to fix it is to get your operands into an appropriate type before acting on them; for example, here are two ways:

Force a number to be treated as floating-point:

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

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

Explicitly supply a decimal scale:

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

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

This set me on the right path, I did have to adjust how many decimal places, but ultimately this was the right answer — ScampDoodle, Apr 09 '13 at 10:13

SQL floating-point precision limited to 6 digits

1 Answers1