Float Errors

Simple way of testing this
CREATE TABLE TEST (COL1 FLOAT)
INSERT INTO TEST (COL1) VALUES (8746.02)
SELECT * FROM TEST
This is the result.
8746.0200000000004
How do I stop this from happening, I am inserting into someone else's system
so I can not change the data type.
Any help would be appreciated.
Thanks,
DanielSorry
SQL Server 2000 SP3
Dan
"Daniel Jeffrey" <daniel@.enprisesolutions.com> wrote in message
news:eFQtcyZ9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Simple way of testing this
> CREATE TABLE TEST (COL1 FLOAT)
> INSERT INTO TEST (COL1) VALUES (8746.02)
> SELECT * FROM TEST
> This is the result.
> 8746.0200000000004
> How do I stop this from happening, I am inserting into someone else's
system
> so I can not change the data type.
> Any help would be appreciated.
> Thanks,
> Daniel
>|||The problem is in the datatype. BOL will tell you that float is an approximate datatype. Which means that the exact number stored is not always what was inteded for storage. There is no way to get around this unless you handle your own rounding (See Round function in BOL). Even then you can get unexpected results.|||... so if you expect to get out what you put in, use an exact datatype, like
a NUMERIC datatype, for instance.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Doug Guerena" <anonymous@.discussions.microsoft.com> wrote in message
news:5D884697-8F47-45A7-88A3-4E0B38DA61DF@.microsoft.com...
> The problem is in the datatype. BOL will tell you that float is an
approximate datatype. Which means that the exact number stored is not
always what was inteded for storage. There is no way to get around this
unless you handle your own rounding (See Round function in BOL). Even then
you can get unexpected results.|||Daniel,
The FLOAT data type can only represent finitely many of the
infinitely-many real numbers. The exact values FLOAT can represent are
those of the form N/power(2,k) where N is an integer with absolute value
between 2^52 and 2^53 and k is an integer between -930 and +1077 (or
something close to this - I didn't verify the exact details). The real
number 8746.02 cannot be written in that form, so the closest
representable float is inserted into the table.
So basically, whoever created the table TEST did not provide a place
to put the exact value 8746.02. If, however, you know that all values
inserted into TEST.COL1 were base-ten decimals with at most 10
significant digits and at most 2 decimal places, the value inserted can
be retrieved with SELECT CAST(COL1 AS DECIMAL(10,2)) FROM TEST, since
there is a unique decimal(10,2) that could have produced each value of
COL1 between -100000000.00 and 100000000.00 in the table.
SK
Daniel Jeffrey wrote:
>Simple way of testing this
>CREATE TABLE TEST (COL1 FLOAT)
>INSERT INTO TEST (COL1) VALUES (8746.02)
>SELECT * FROM TEST
>This is the result.
>8746.0200000000004
>How do I stop this from happening, I am inserting into someone else's system
>so I can not change the data type.
>Any help would be appreciated.
>Thanks,
>Daniel
>
>|||I have found rounding issues with this operation as wellsql