0

I've been reading around the subject of floating point numbers and how they can lead to inaccuracies in calculations due to their binary nature, and as such they should never be used for financial applications where the DECIMAL datatype is preferred.

Is there ever a situation where it might be preferable to use a float over a decimal?

harryg
  • 23,311
  • 45
  • 125
  • 198
  • if you need to work with very large numbers you are likely to use float rather than decimal because of the increased range (depending on the maximum scale of decimal your database supports). – mc110 Jun 27 '14 at 14:52

3 Answers3

5

Calculations on decimal also yield inaccuracies. What matters is whether there are specified rounding rules, such as in financial applications, and what are the operations involved. Basically, it all depends on the context.

If the context is to follow the IEEE 754 rules in binary, then using (binary) floats is preferable.

vinc17
  • 2,829
  • 17
  • 23
2

As you may have read, for financial uses, many databases support special types such as money or smallmoney to provide for better data retention. Source

However, through some digging, I've found a pretty clear-cut answer to your question. Basically, it depends on the implementation and the needs of the data. If the data you're storing is to be manipulated (especially exponentially) stay away from floating type data.

It all depends on the accuracy you need to maintain in your data. Converting 1.1 to a float and then convert it back again, your result will be something like 1.0999999999989.

As my professors always said, and as another user points on here, this is a basic rule-of-thumb of when to use a floating type:

For values which are more artefacts of nature which can't really be measured exactly anyway, float/double are more appropriate. For example, scientific data would usually be represented in this form. Here, the original values won't be "decimally accurate" to start with, so it's not important for the expected results to maintain the "decimal accuracy". Floating binary point types are much faster to work with than decimals.

Community
  • 1
  • 1
NeatoBandito
  • 110
  • 1
  • 10
  • 1
    In itself, the sentence “If the data you're[sic] storing is to be manipulated (especially exponentially) stay away from floating type data” is nonsense. Decimal is not more accurate than binary. It is accurate for different rationals. – Pascal Cuoq Jun 27 '14 at 19:07
  • I'm not trying to imply that decimal is more accurate than binary, rather that it is generally not a good approach when dealing with currency. I guess I wasn't very clear in my statement. – NeatoBandito Jun 27 '14 at 21:18
2

If you have stock option data, the prices could be stored as decimal, but there are derived quantities such as implied volatility that are computed by inverting a formula and which are not know precisely to a fixed number of decimal places. It makes sense to store them as floats. Another example is that the price of a bond may be stored as a decimal (although some bonds still trade in fractions, I believe), but a derived quantity such as yield-to-maturity is again a floating point number.

Fortranner
  • 2,525
  • 2
  • 22
  • 25