0

I set up a new etl job in SSIS. The currency fields are all defined as decimal(17,2) data type. When the results are exported to excel, those fields appear to have extended decimals beyond 2. It does not visually exist in the fields, but when you do a sum of the column they show up in the result. The data used to populate those currency fields have the same data type, but I am storing the records as a summary, not at the transaction level. I initially thought this was an excel problem, so I ran a test in sql and came up with unexpected results.

How is it possible if sql is only storing 2 decimal places, to output 10+ decimal places from a sum function? I tried to do some research on this and I didn't find anything.

I have so-so corrected the results by doing a cast(round(myvalue,3) as decimal(17,2)). there are still extended decimal places in my output, but it is small enough that it doesn't cause a balancing issue.

-----------multiple records----------------

create table #MyPi (MyPi decimal(17,2))
Declare @mycounter int
set @mycounter =1 

while @mycounter <> 500000
begin
insert into #MyPi(Mypi)
values (round(3.14159,3))
set @mycounter = @myCounter +1 
end

-------------Single Record----------------

create table #MyPi2 (MyPi decimal(17,2))
insert into #MyPi2(Mypi)
values (3.14159)

Results of my test (500k records)

select  sum(Mypi) SumMyPi, sum(cast(Mypi as float))SumMyPiAsFloat from #MyPi

SumMyPi         SumMyPiAsFloat
1569996.86  1569996.85998705

1 record

select  Mypi, cast(Mypi as float) MypiAsFloat from #MyPi2

Mypi    MypiAsFloat
3.14    3.14
Hadi
  • 36,233
  • 13
  • 65
  • 124
Tamera
  • 1
  • 3
  • So the only result that is more than 2 decimal places is when you're casting MyPi as a float. Floating point data types are not precise. As soon as the data is cast into a float, it takes on an arbitrary number of decimal places as is necessary to accurately model the data in memory. Then you're summing that new float value. Hence the additional decimal places. – DeadZone May 24 '19 at 17:08
  • Where is it getting the values to populate the additional decimal places? 2.22 + 2.22 is 4.44 not 4.44000001326. The extract I created is not casting the currency values as a float, you can't see the addition decimal places in the field, but when you sum the column the total has extended decimal places. – Tamera May 24 '19 at 17:44
  • you need to understand how a computer stores floating point numbers. Try [this stackOverflow question](https://stackoverflow.com/questions/7644699/how-are-floating-point-numbers-are-stored-in-memory). It should help illuminate things for you. – DeadZone May 24 '19 at 18:14
  • I guess I am not explaining clearly. The value is not stored as a floating point. It is stored as a decimal(17,2). Perhaps my test to see what was happening was poorly executed. – Tamera May 24 '19 at 18:22
  • Right, but you're casting it as a float. So you're turning it into a float and then summing it. As soon as you cast it as a float, it is no longer a decimal(17,2). Instead it is a floating point representation of the number that used to be a decimal(17,2). – DeadZone May 24 '19 at 18:27
  • Converting `3.14159` to a `decimal(17, 2)` will get you `3.14`. You have stored exactly 499999 of these in the table, and `499999 * 3.14` is precisely `1569996.86`, when evaluated as a `decimal`, so that result is perfectly accurate. When you're casting to `float` (either the values themselves or the end result) the usual caveats of binary floating point apply: `3.14` cannot be represented exactly, and you get cumulative rounding errors if you sum them. The default string representation will pretend `3.14` is exact, but it is not. `SELECT FORMAT(3.14e, 'G17')` is `3.1400000000000001` – Jeroen Mostert May 24 '19 at 18:32
  • Note that Excel exclusively uses floating-point to represent numbers (unless you explicitly store values as text) so even if all calculations in SQL Server yielded perfectly accurate results, you could still end up with rounding issues in Excel. Excel tries even harder than SQL Server to pretend its values are accurate, by not showing excess digits for very small differences very close to 0, but that won't change the fact that most numbers aren't exactly represented under the covers. – Jeroen Mostert May 24 '19 at 18:38

0 Answers0