SQL floating-point precision limited to 6 digits
excel, excel-formula, sql, t-sql
Solution
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
Problem
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?