Float Issues

  • Comments posted to this topic are about the item Float Issues

  • Good one!

    The problem is that casting to int always removes any decimals, and with the example, 0.289 is actually stored as a value which, when converted with the maximum number of decimals becomes 0.2889999999999999791278071370470570400. Rounding that to 2 decimals while remaining a float actually leaves it at 0.2899999999999999800159855567471822724, so ever so slightly less than 0.29.

    Multiplying that by 100.0 and then lopping off the decimal portion by casting it to INT turns it into 28.

    Floats are fine, as long as you understand the fuzziness when comparing to integers (or rather, to precise numbers). Using it for monetary amounts, however, is a recipe for pain and suffering!

    • This reply was modified 4 years, 1 month ago by  Rune Bivrin.


    Just because you're right doesn't mean everybody else is wrong.

  • The interesting thing is that rounding to 1 decimal the result is 30.

    DECLARE @tmp1 FLOAT = 0.289;
    SELECT
    CONVERT(INT, ROUND(@tmp1, 1) * 100.0)
  • I am intested to know how the people who answered this correctly figured it out 🙂

  • Well, 29 would have been too easy, and the title of the QOTD hinted at the result not being quite the obvious. Casting to INT always truncates decimals, so it would reasonably be lower than the expected value.


    Just because you're right doesn't mean everybody else is wrong.

  • I got it wrong, but 42, which is the answer to life, the universe, and everything was not an option for this question. At any rate I learned another valuable lesson about dealing with non-integer values. Thank you.

     

    Luther

     

  • Chris Jones wrote:

    I am intested to know how the people who answered this correctly figured it out 🙂

    Run the code 🙂 Then try to figure out what the hell SQL did wrong...

     

  • Rune Bivrin wrote:

    Good one!

    The problem is that casting to int always removes any decimals, and with the example, 0.289 is actually stored as a value which, when converted with the maximum number of decimals becomes 0.2889999999999999791278071370470570400. Rounding that to 2 decimals while remaining a float actually leaves it at 0.2899999999999999800159855567471822724, so ever so slightly less than 0.29.

    Multiplying that by 100.0 and then lopping off the decimal portion by casting it to INT turns it into 28.

    Floats are fine, as long as you understand the fuzziness when comparing to integers (or rather, to precise numbers). Using it for monetary amounts, however, is a recipe for pain and suffering!

    Good job on explanation. 1/10 cannot be represented exactly in floating point. Not saying I didn't pick 29 myself LOL

     

  • Yes, a strange result. Try this:

    DECLARE @tmp1 FLOAT = 0.289;
    SELECT CONVERT(decimal(38,36),ROUND(@tmp1, 2)*100),
    CONVERT(decimal(38,36),ROUND(@tmp1, 2))*100
  • Jonathan AC Roberts wrote:

    Yes, a strange result. Try this:

    DECLARE @tmp1 FLOAT = 0.289;
    SELECT CONVERT(decimal(38,36),ROUND(@tmp1, 2)*100),
    CONVERT(decimal(38,36),ROUND(@tmp1, 2))*100

    Well, that's no more strange than any of the other examples, really. As a general rule, with floating point values, what you see isn't quite what is used for calculations. It's almost always a bit imprecise in the least significant digits.

    To some extent, it gets worse with larger numbers. Try this:

    DECLARE @tmp1 FLOAT = 10e27;
    SELECT CONVERT(decimal(38,2),ROUND(@tmp1, 2)*100),
    CONVERT(decimal(38,2),ROUND(@tmp1, 2))*100,
    CONVERT(decimal(38,2),ROUND(@tmp1, 2)*100.0),
    CONVERT(decimal(38,2),ROUND(@tmp1, 2))*100.0

    That can get really, really confusing... Notice the extra decimal place in the last column!


    Just because you're right doesn't mean everybody else is wrong.

  • Rune Bivrin wrote:

    To some extent, it gets worse with larger numbers. Try this:

    DECLARE @tmp1 FLOAT = 10e27;
    SELECT CONVERT(decimal(38,2),ROUND(@tmp1, 2)*100),
    CONVERT(decimal(38,2),ROUND(@tmp1, 2))*100,
    CONVERT(decimal(38,2),ROUND(@tmp1, 2)*100.0),
    CONVERT(decimal(38,2),ROUND(@tmp1, 2))*100.0

    That can get really, really confusing... Notice the extra decimal place in the last column!

    I think the extra decimal digit is because if you multiply a number with 2 decimal places by a number with 1 decimal place, the result can have 3 decimal places.

  • Jonathan AC Roberts wrote:

    I think the extra decimal digit is because if you multiply a number with 2 decimal places by a number with 1 decimal place, the result can have 3 decimal places.

    Yes, that's exactly why. It's predictable, but one needs to remember the inherently useless nature of that extra decimal. I think my old maths teacher would nod approvingly, while the physics teacher would roll his eyes.


    Just because you're right doesn't mean everybody else is wrong.

  • Money type converts as desired/expected.

    DECLARE @tmp1 money = 0.289;

    SELECT

    CONVERT(INT,

    (ROUND(@tmp1, 2) * 100.0)

    )

     

  • Yes, because MONEY is a precise data type with 4 decimal places. It's implemented as a 64-bit integer, scaled down by 10000. That and DATETIME must be the two lamest data types in SQL Server (apart from SMALLDATETIME and SMALLMONEY, of course. They were conceived by nincompoops, in my hones opinion!)


    Just because you're right doesn't mean everybody else is wrong.

  • Rune Bivrin wrote:

    Yes, because MONEY is a precise data type with 4 decimal places. It's implemented as a 64-bit integer, scaled down by 10000. That and DATETIME must be the two lamest data types in SQL Server (apart from SMALLDATETIME and SMALLMONEY, of course. They were conceived by nincompoops, in my hones opinion!)

    Actually, MONEY is useful if only for documentation purposes. You could use decimal but why would you want to? Being an integer makes the type faster than decimal.

    DATETIME does have a few rough edges, but is very useful. What objection do you have to it?

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply