January 16, 2020 at 12:00 am
Comments posted to this topic are about the item Float Issues
January 16, 2020 at 8:49 am
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!
January 16, 2020 at 9:21 am
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)
January 16, 2020 at 10:07 am
I am intested to know how the people who answered this correctly figured it out 🙂
January 16, 2020 at 10:11 am
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.
January 16, 2020 at 1:17 pm
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
January 16, 2020 at 2:05 pm
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
January 16, 2020 at 2:18 pm
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
January 16, 2020 at 2:29 pm
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!
January 16, 2020 at 2:39 pm
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.0That 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.
January 16, 2020 at 2:44 pm
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.
January 16, 2020 at 2:59 pm
Money type converts as desired/expected.
DECLARE @tmp1 money = 0.289;
SELECT
CONVERT(INT,
(ROUND(@tmp1, 2) * 100.0)
)
January 16, 2020 at 3:06 pm
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!)
January 16, 2020 at 3:09 pm
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