August 11, 2015 at 11:52 pm
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
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
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
Nice question that illustrates approximate numerics. Thanks.
August 12, 2015 at 6:48 am
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
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
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
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
Nice question, Naveen! A good example at the importance of choosing the correct datatypes. Thanks.
August 12, 2015 at 10:33 am
Enjoyed the question. Always a good reminder.
August 12, 2015 at 11:35 am
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
Very, very nice. Thanks, Naveen!
August 12, 2015 at 12:38 pm
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