4

I have the following code:

SELECT -701385.10 -- -701385.10
SELECT SUM(-701385.10) -- -701385.10
SELECT -701385.10/2889991754.89 -- -0.000242694498630
SELECT SUM(-701385.10)/2889991754.89 -- -0.000242

In the last SELECT the result is truncated to 6 decimal places. I've read through the Precision, Scale, and Length article and unless my working is wrong, I can't understand why the truncation is occurring. The type of the expression SUM(-701385.10) should be DECIMAL(38,2) - see SUM - so the type resulting from the division should have:

Precision:

  • p1 - s1 + s2 + max(6, s1 + p2 + 1)
  • 38 - 2 + 2 + max(6, 2 + 10 + 1)
  • 38 - max(6,13)
  • 38 - 13
  • 25

Scale:

  • max(6, s1 + p2 + 1)
  • max(6, 2 + 10 + 1)
  • max(6, 13)
  • 13

So why are the decimal places being truncated?

Tom Hunter
  • 5,714
  • 10
  • 51
  • 76
  • possible duplicate of [T-SQL Decimal Division Accuracy](http://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy) – gbn Nov 27 '11 at 11:14

1 Answers1

4

Your working is wrong

Precision: p1 - s1 + s2 + max(6, s1 + p2 + 1)
Scale: max(6, s1 + p2 + 1)

Gives

Precision: 38 - 2 + 2 + max(6, 2 + 12 + 1) = 53
Scale: max(6, 2 + 12 + 1)                  = 15

Which is greater than 38 so you are getting truncation as covered here

Martin Smith
  • 438,706
  • 87
  • 741
  • 845