Can anyone explain why this comparison fails when casting from float?

Question

I'm using Microsoft Server SQL.

I'm not sure why this is happening. My only geuss so far is that the value is 32.49999 under the hood or something. But the same equation does not fail when using different values like 32.32 etc.

Can anyone please explain why this is happening?

select 
cast(32.55 as float)
,cast(32.55 as float) * 1000
,case when cast(32.55 as float) * 1000 <> 32550 then 'F' ELSE 'C' END as [vanilla]
,case when cast(cast(32.55 as float) * 1000 as int) <> cast(32550 as int) then 'F' ELSE 'C' END as [int]
,case when cast(cast(32.55 as float) * 1000 as varchar(max)) <> cast(32550 as varchar(max)) then 'F' ELSE 'C' END as [varchar]
,case when cast(cast(32.55 as float) * 1000 as float) <> cast(32550 as float) then 'F' ELSE 'C' END as [float]
,case when round(cast(32.55 as float) * 1000,0) <> round(32550,0) then 'F' ELSE 'C' END as [round]
,case when cast(cast(32.55 as float) * 1000 as decimal(18,2)) <> cast(32550 as decimal(18,2)) then 'F' ELSE 'C' END as [decimal]

The float and real data types are known as approximate data types - source online documentation — mcha, Jul 12 '16 at 09:50
@ChrisPickford Replacing float with decimals is a no-go. I'm using an existing table in my real world scenario. Also i'd expect casting to integer to work. — user1026622, Jul 12 '16 at 09:53
@mcha Still doesn't really explain why 32.32 would work though — user1026622, Jul 12 '16 at 09:56
What do your existing float values represent? If they are supposed to be real numbers (e.g. sales data, dimensions, anything with an actual value) then your data model is very flawed. As @mcha mentioned, the values stored by float are approximate. See http://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server. — Chris Pickford, Jul 12 '16 at 10:06
@ChrisPickford Yeah, they are supposed to be real numbers (In this case, they represent the weight of an item in a sales order). Unfortunately, changing the datatype is not an option. I'm using an existing piece of software (Which I cannot modify) to show some sales data. Though I definitly agree with both of you, it'd be allot better to just use a decimal. — user1026622, Jul 12 '16 at 11:46
float works as the summation of 'powers of 2', not powers of 10 - it's all a trick to allow very small and very large numbers without resorting to a massive number of bytes - and the cost is round-off - it just so happens that 32 is a power of 2 - coincidence? Yes it probably is, but you might find that 32.32 is held as equal to, or slightly more, e.g. 32.320000001 and does nor tound down in error — Cato, Jul 12 '16 at 12:18
how about using a WITH statement where you ROUND to an appropriate number of decimals and CAST results to DECIMAL? Then you have a ready made set of fields behaving as decimals not floats. — Cato, Jul 12 '16 at 12:22

Cato · Answer 1 · 2016-07-12T16:09:16.363

it's due to 'round down' try this select cast(cast(32.55 as float) * 1000 as int)

I get 32549

your 32550 is being held as 32549.99999... and rounds downwards

you need to ROUND to zero decimal places, even if you think you are dealing with integers before you do this.

e.g

select cast(ROUND(cast(32.55 as float) * 1000,0) as int)

for your example called FLOAT - there are 'hidden' decimals that are different, meaning that if one is subtracted from the other, you get a tiny answer that is not zero

TRY THIS

    select 
    cast(cast(32.55 as float) * 1000 as float), 
    cast(32550 as float),
    cast(cast(32.55 as float) * 1000 as float) -  cast(32550 as float),  --this gives non zero answer, indicating that the internal values have different values in the higest decimal places
    case when cast(cast(32.55 as float) * 1000 as float) <> cast(32550 as float) then 'F' ELSE 'C' END as [float]

Can anyone explain why this comparison fails when casting from float?

1 Answers1