Post reply

Real to Float

  • August 11, 2015 at 11:52 pm

    #299558

    Comments posted to this topic are about the item Real to Float

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

  • August 11, 2015 at 11:52 pm

    #1819213

    Good question, thanx.

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • August 12, 2015 at 1:13 am

    This was removed by the editor as SPAM

  • August 12, 2015 at 2:23 am

    #1819229

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • August 12, 2015 at 5:09 am

    #1819250

    Nice question that illustrates approximate numerics. Thanks.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • August 12, 2015 at 5:23 am

    #1819260

    Good question.

    So, what's the correct way to do the conversion?

    DECLARE @real REAL= 43.39061;

    SELECT @real

    SELECT CONVERT(FLOAT, @real)

    SELECT CAST(@REAL AS FLOAT)

  • August 12, 2015 at 6:48 am

    #1819296

    wedneyyuri (8/12/2015)

    Good question.

    So, what's the correct way to do the conversion?

    DECLARE @real REAL= 43.39061;

    SELECT @real

    SELECT CONVERT(FLOAT, @real)

    SELECT CAST(@REAL AS FLOAT)

    If you care about maintaining the value the right way is to use an exact numeric instead of an approximate one. 🙂

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 12, 2015 at 7:10 am

    #1819306

    Great question.

    Trying to get exact accuracy from REAL/FLOAT and relying on DATETIME for exact time (.997ms), all have to be careful with!

  • August 12, 2015 at 8:29 am

    #1819336

    Between this question and the one on varchar conversion the takeaway is never trust implicit conversion? To be honest once the real was truncated the value should have remained the same once the implicit conversion occurred. That it doesn't is damning.

    Is this an artifact of the IEEE definition of the two data types, or a bug in SQL Server? Or the fact it's going through yet another data type in the process of conversion?

    Either way I'm hearing "NEVER MIX DATATYPES". Full Stop.

    Would that be an accurate lesson to learn from this?

    The more I learn about T/SQL the less I trust it!

  • August 12, 2015 at 8:45 am

    #1819343

    roger.plowman (8/12/2015)

    Between this question and the one on varchar conversion the takeaway is never trust implicit conversion? To be honest once the real was truncated the value should have remained the same once the implicit conversion occurred. That it doesn't is damning.

    Is this an artifact of the IEEE definition of the two data types, or a bug in SQL Server? Or the fact it's going through yet another data type in the process of conversion?

    Either way I'm hearing "NEVER MIX DATATYPES". Full Stop.

    Would that be an accurate lesson to learn from this?

    The more I learn about T/SQL the less I trust it!

    No. The issue here is that the value was truncated when it was a REAL. Then the value changed a little bit when being inserted in a FLOAT. The real takeaway from this is that if you care about the values, don't use approximate datatypes. If you look at the explanation of the datatypes you will see that REAL is a FLOAT(24) and FLOAT, when no precision is defined is is FLOAT(53).

    https://technet.microsoft.com/en-US/library/ms187752.aspx

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 12, 2015 at 9:05 am

    #1819357

    Nice question, Naveen! A good example at the importance of choosing the correct datatypes. Thanks.

  • August 12, 2015 at 10:33 am

    #1819414

    Enjoyed the question. Always a good reminder.

  • August 12, 2015 at 11:35 am

    #1819449

    These questions are always fun, since I have to do some playing with binary representations to make sure I fully understand what's going on. Fortunately in this case it's pretty straightforward.

    The trick here is that while REAL will never display more than 7 digits, the 24 bit mantissa allows it to store representations of values that go far beyond 7 decimal digits. Combined with the fact that many decimal fractions have no exact representation in binary, you can get results like this.

    In this case, 43.3906060284202 represented out to 24 binary digits is 101011.011000111111111011, which converted back to decimal is 43.39060592651367187500. Rounding to 15 digits, we get 43.3906059265137, the answer. It's not really a magical addition of digits; the initial value stored for the REAL already represented this number. When converted to float, we now get to see it rounded to 15 decimal digits instead of 7.

    It's important to note this doesn't really have to do with the fact that you're converting to the approximate float type, or the visible truncation of the decimal number to 7 digits by assigning the value to REAL. It has to do with the inexact binary representation of the decimal fraction, coupled with the availability of only 24 bits to store it.

    With respect to the target data type, if @real is converted to DECIMAL (15,13), you get the same result, for the same reason. The binary representation to 24 digits of precision doesn't exactly represent the original decimal fraction.

    With respect to the truncation of the decimal version of the number, you can confirm that by changing the value to a simple decimal fraction that has no exact representation in binary, 0.1.

    DECLARE @real REAL=0.1

    SELECT CAST(@real as FLOAT)

    SELECT CAST(@real as DECIMAL(38,37))

    If the initial variable declaration is for a float, having 53 bits for the mantissa means that it takes a bit more work to find numbers whose binary representations are truncated in a way that results in a different number being displayed, but it's still not too hard.

    DECLARE @float FLOAT=9999.1

    SELECT @float

    SELECT CAST(@float as DECIMAL(38,34))

    Cheers!

  • August 12, 2015 at 11:42 am

    #1819453

    Very, very nice. Thanks, Naveen!

  • August 12, 2015 at 12:38 pm

    #1819481

    Jacob Wilkins (8/12/2015)

    These questions are always fun, since I have to do some playing with binary representations to make sure I fully understand what's going on. Fortunately in this case it's pretty straightforward.

    The trick here is that while REAL will never display more than 7 digits, the 24 bit mantissa allows it to store representations of values that go far beyond 7 decimal digits. Combined with the fact that many decimal fractions have no exact representation in binary, you can get results like this.

    In this case, 43.3906060284202 represented out to 24 binary digits is 101011.011000111111111011, which converted back to decimal is 43.39060592651367187500. Rounding to 15 digits, we get 43.3906059265137, the answer. It's not really a magical addition of digits; the initial value stored for the REAL already represented this number. When converted to float, we now get to see it rounded to 15 decimal digits instead of 7.

    It's important to note this doesn't really have to do with the fact that you're converting to the approximate float type, or the visible truncation of the decimal number to 7 digits by assigning the value to REAL. It has to do with the inexact binary representation of the decimal fraction, coupled with the availability of only 24 bits to store it.

    With respect to the target data type, if @real is converted to DECIMAL (15,13), you get the same result, for the same reason. The binary representation to 24 digits of precision doesn't exactly represent the original decimal fraction.

    With respect to the truncation of the decimal version of the number, you can confirm that by changing the value to a simple decimal fraction that has no exact representation in binary, 0.1.

    DECLARE @real REAL=0.1

    SELECT CAST(@real as FLOAT)

    SELECT CAST(@real as DECIMAL(38,37))

    If the initial variable declaration is for a float, having 53 bits for the mantissa means that it takes a bit more work to find numbers whose binary representations are truncated in a way that results in a different number being displayed, but it's still not too hard.

    DECLARE @float FLOAT=9999.1

    SELECT @float

    SELECT CAST(@float as DECIMAL(38,34))

    Cheers!

    Thanks Jacob, this is really useful. Amazing explanation.

    Thanks,
    Naveen.
    Every thought is a cause and every condition an effect

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

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