Floating point fun

I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
migration. I've read certain floating-point values can't be represented
accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001? Converting the column to DECIMAL is probably not an
option.
Thanks
Damien
CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
FLOAT(2) )
-- Try and insert the value 4.1 into the float table
INSERT INTO #float_test ( ft_id, rate1 )
SELECT 1, 4.1 UNION
SELECT 2, 4 + .1 UNION
SELECT 3, '4.10' UNION
SELECT 4, 4.11 UNION
SELECT 5, 4.95 UNION
SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
SELECT 7, .1
GO
UPDATE #float_test
SET rate2 = rate1
SELECT * FROM #float_test
DROP TABLE #float_testHi
Read http://www.aspfaq.com/show.asp?id=2477
"Damien" <Damien@.discussions.microsoft.com> wrote in message
news:C4AF11D4-0AD6-4EE3-8453-FFF203829E25@.microsoft.com...
> I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
> migration. I've read certain floating-point values can't be represented
> accurately, however, .1 doesn't look like one of them as the FLOAT(2)
> field
> copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1
> round
> to .10000000000000001? Converting the column to DECIMAL is probably not
> an
> option.
> Thanks
> Damien
> CREATE TABLE #float_test ( ft_id INT PRIMARY KEY, rate1 FLOAT, rate2
> FLOAT(2) )
> -- Try and insert the value 4.1 into the float table
> INSERT INTO #float_test ( ft_id, rate1 )
> SELECT 1, 4.1 UNION
> SELECT 2, 4 + .1 UNION
> SELECT 3, '4.10' UNION
> SELECT 4, 4.11 UNION
> SELECT 5, 4.95 UNION
> SELECT 6, CONVERT( REAL, 4.1, 0 ) UNION
> SELECT 7, .1
> GO
> UPDATE #float_test
> SET rate2 = rate1
> SELECT * FROM #float_test
> DROP TABLE #float_test
>|||On Tue, 23 Aug 2005 01:43:07 -0700, Damien
<Damien@.discussions.microsoft.com> wrote:

>I'm trying to INSERT the value 4.1 into a FLOAT field as part of a data
>migration. I've read certain floating-point values can't be represented
>accurately, however, .1 doesn't look like one of them as the FLOAT(2) field
>copes with it. Also, why does 4.1 round to .0999999999999996 but 0.1 round
>to .10000000000000001? Converting the column to DECIMAL is probably not an
>option.
It's not clear to me what you expect to happen. Floating-point values
*are* precisely represented by floating-point values. (cough)
OTOH, certain fractional values are not in the domain of certain
floating-point data types. In those cases, software generally picks
the closest value available. (The result is "error of approximation",
not a rounding error.)
You seem to think that every number that ends in '.1' should have the
same behavior. That's simply not true of floating-point data types.
It might help to think of it this way. Neighboring, distinct values in
exact data types are the same distance apart on a number line. That
is, each value in a SQL INTEGER data type is plus or minus 1 from its
neighbor.
But neighboring, distinct values in a floating-point data type are not
the same distance apart on a number line. The closer you get to zero
(from either direction), the more closely spaced the neighboring
distinct values are.
Mike Sherrill|||> Also, why does 4.1 round to .0999999999999996 but 0.1 round
to .10000000000000001?
because under the hood floats are stored as binaries. So, binary
numbers are represented accurately, up to some accuracy, of course.
Decimals are rounded to binaries. When you convert binaries back to
decimals, expect some mismatch|||On 24 Aug 2005 14:36:10 -0700, ford_desperado@.yahoo.com wrote:
[snip]
>Decimals are rounded to binaries.
Error of approximation, which seems to be what you're stumbling
toward, doesn't mean "fixed point numbers are rounded to binary". See
Knuth, vol 2.