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 approxim
ate datatype. Which means that the exact number stored is not always what w
as inteded for storage. There is no way to get around this unless you handl
e your own rounding (See Ro
und 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, lik
e
a NUMERIC datatype, for instance.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.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 syste
m
>so I can not change the data type.
>Any help would be appreciated.
>Thanks,
>Daniel
>
>|||I have found rounding issues with this operation as well

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

Flat file connection manager question

Hi,

I am having problems setting up columns in a flat file connection manager.

The text file I am trying to parse is in this format:

col1 col2 col3 col4 [] col1 col2 col3 col4 [] col1 col2 col3 col4

where [] = {LF}

I've tried all of the different column delimiters, but apparently this file does not use any of the built-in delimiters, such as tab. I think there are just blank spaces between the columns.

I've requested that the file be comma-delimited instead, but in the event that this is not possible, how should I handle this situation?

Thank you

Are you sure it isn't a fixed-width format?|||

Actually I think it is.

|||Yep, start there and see how far you get. Try working with either the fixed-width format or the ragged-right format.