chriscoates (12/10/2009)
Thanks sknox for some good explanations here. To check I've got this straight ...The internal casts give us 10 decimal places.
That effectively boils down to:
SELECT ( 1.6757400000 / 10000.0000000000)
For the various reasons described above, SQL decides to truncate this to a 6 decimal place number: 0.000167
The outer cast happens after the division, turning this into a ten decimal place number: 0.0001670000
For the record, I think that's a bit pants. Not what I would have expected.
Good QotD. I've learnt something this morning! I'll definitley have to remember this one. My company does a lot of financial transactions, and we always explicitly cast numbers in an effort to retain lots of decimal places, not lose them. We have come across numbers not adding up correctly before, but were generally unable to track the root cause. I've quite often seen numbers multiplied by a factor so that we work with larger numbers. For example, pounds and pence displayed to 6 decimal places are often stored as decimal pence, rather than decimal pounds.
By Jove, I think you've got it! 😛
Not what most of us would expect. I'd expect treatment consistent with http://en.wikipedia.org/wiki/Significant_figures. I believe it comes down to performance -- it's much faster to apply a simple set of equations than to inspect the values and select the relevant parameters -- especially when there's the base-2 to base-10 conversions necessary to identify the locations of the significant digits.