inserting single precision data into sql server float column

Hi,
iam using the bcp api to load data into sql server. The data to be loaded
is single precision and hence my bcp_bind type is SQLFLT4. The column in my
sql server table is a FLOAT(which is of course double precision).
If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
I mean the documentation says that implicit conversion for these types are
allowed. So iam not sure why this happens.
Appreciate any inputs.
VivekHi Vivek,
Thats the way float works:
"Approximate-number data types for use with floating point numeric
data. Floating point data is approximate; therefore, not all values in
the data type range can be represented exactly. "
DECLARE @.SOMEValue Float(2)
SEt @.SomeValue = 1.100001
SELECT @.SOMEValue
For more precicion you have to use another database like decimal.
HTH, Jens Suessmeyer.|||Because a float in SQL Server is an *approximate* floating point
representation, essentially meaning if you round it to the appropriate
number of significant digits then you'll get the number you're after but
it's only stored as accurately as the binary numbering system can manage
(defined by IEEE 754). The same would happen if you used real rather
than float. I think what you're after is an *exact* floating point
representation, which corresponds to the numeric (or decimal) data types
in SQL Server (i.e. fixed precision & scale).
See Using decimal, float and real data
<http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
SQL Books Online.
*mike hodgson*
http://sqlnerd.blogspot.com
Vivek wrote:

>Hi,
> iam using the bcp api to load data into sql server. The data to be loaded
>is single precision and hence my bcp_bind type is SQLFLT4. The column in my
>sql server table is a FLOAT(which is of course double precision).
>If i try to insert say a value 73.22 it gets inserted as 73.22000122070313.
>I mean the documentation says that implicit conversion for these types are
>allowed. So iam not sure why this happens.
>Appreciate any inputs.
>Vivek
>|||I think my query was not stated clearly. If i load the same value into a
REAL column it shows exactly what i inserted. (73.22)
Similarily if i store that value in a double precision program variable and
load into a FLOAT column it shows exactly what i stored.
The problem is when the value is in a single precision program variable and
i load into a FLOAT
"Mike Hodgson" wrote:

> Because a float in SQL Server is an *approximate* floating point
> representation, essentially meaning if you round it to the appropriate
> number of significant digits then you'll get the number you're after but
> it's only stored as accurately as the binary numbering system can manage
> (defined by IEEE 754). The same would happen if you used real rather
> than float. I think what you're after is an *exact* floating point
> representation, which corresponds to the numeric (or decimal) data types
> in SQL Server (i.e. fixed precision & scale).
> See Using decimal, float and real data
> <http://msdn.microsoft.com/library/e...con_03_6mht.asp> in
> SQL Books Online.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Vivek wrote:
>
>|||> I think my query was not stated clearly. If i load the same value into a
> REAL column it shows exactly what i inserted. (73.22)
We understood the question. You do not understand the issue. Did you read
BOL regarding their definition and usage (Accessing and Changing Relational
Data / Using decimal, float, and real Data)? If so, then contine with
http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
representation of a value with the actual value. What you claim to see can
also be an artifact of whatever technique you use to "see" the value after
storage in the database. Below is a script that demonstrates the problem
more clearly.
set nocount on
declare @.test1 real, @.test2 float, @.test3 float(2)
set @.test1 = 73.22
set @.test2 = 73.22
set @.test3 = 73.22
select @.test1, @.test2, @.test3
select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
cast(@.test3 as varbinary(8))
print @.test1
print @.test2
print @.test3|||That's what i thought initially. (I use Query Analyzer btw) After inserting
the same value (from a double precision and single precision variable
respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
this output:
73.22
73.22000122070313
Would you say that the actual values of both rows are the same regardless of
what i see above?
The binary values are:
0x40524E147AE147AE
0x40524E1480000000
"Scott Morris" wrote:

> We understood the question. You do not understand the issue. Did you re
ad
> BOL regarding their definition and usage (Accessing and Changing Relationa
l
> Data / Using decimal, float, and real Data)? If so, then contine with
> http://docs.sun.com/source/806-3568/ncg_goldberg.html. Do not confuse
> representation of a value with the actual value. What you claim to see ca
n
> also be an artifact of whatever technique you use to "see" the value after
> storage in the database. Below is a script that demonstrates the problem
> more clearly.
> set nocount on
> declare @.test1 real, @.test2 float, @.test3 float(2)
> set @.test1 = 73.22
> set @.test2 = 73.22
> set @.test3 = 73.22
> select @.test1, @.test2, @.test3
> select cast(@.test1 as varbinary(8)), cast(@.test2 as varbinary(8)),
> cast(@.test3 as varbinary(8))
> print @.test1
> print @.test2
> print @.test3
>
>|||On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:

>That's what i thought initially. (I use Query Analyzer btw) After inserting
>the same value (from a double precision and single precision variable
>respectively) into a FLOAT column, if i do a 'select * from tfloat' i get
>this output:
>73.22
>73.22000122070313
>Would you say that the actual values of both rows are the same regardless o
f
>what i see above?
>The binary values are:
>0x40524E147AE147AE
>0x40524E1480000000
Hi Vivek,
These binary values explainexactly what's going on.
The closest representation in a double precision representation is,
obviosuly, 0x40524E147AE147AE. When you store that in a single precision
variable or column, it has to be rounded to the closest that can be
represented in the 24 bits set aside for single precision, which is
apparently 0x40524E148. If you then store this in a double precision
column, the extra bits are added again - but of course as 0 bits, since
SQL Server has no memory of the bits that were prreviously lost. And so
it ends up as 0x40524E1480000000.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
insertions and stick to single to single and double to double precision
insertions?
"Hugo Kornelis" wrote:

> On Mon, 23 Jan 2006 03:39:03 -0800, Vivek wrote:
>
> Hi Vivek,
> These binary values explainexactly what's going on.
> The closest representation in a double precision representation is,
> obviosuly, 0x40524E147AE147AE. When you store that in a single precision
> variable or column, it has to be rounded to the closest that can be
> represented in the 24 bits set aside for single precision, which is
> apparently 0x40524E148. If you then store this in a double precision
> column, the extra bits are added again - but of course as 0 bits, since
> SQL Server has no memory of the bits that were prreviously lost. And so
> it ends up as 0x40524E1480000000.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:

>Thanks Hugo. That sounds reasonable. So do i just avoid these kind of
>insertions and stick to single to single and double to double precision
>insertions?
Hi Vivek,
I don't know what the requirements of your applications are. But as a
rule of thumb, I'd recommend to avoid conversions as much as possible,
stick to the same precision. Once you've lost precision, there's no way
to get it back. But OTOH, storing data at more than required precision
is just a waste of space.
Find the precision you need, then design your DB and application around
that.
Hugo Kornelis, SQL Server MVP|||Thank you guys.
"Hugo Kornelis" wrote:

> On Mon, 23 Jan 2006 20:35:02 -0800, Vivek wrote:
>
> Hi Vivek,
> I don't know what the requirements of your applications are. But as a
> rule of thumb, I'd recommend to avoid conversions as much as possible,
> stick to the same precision. Once you've lost precision, there's no way
> to get it back. But OTOH, storing data at more than required precision
> is just a waste of space.
> Find the precision you need, then design your DB and application around
> that.
> --
> Hugo Kornelis, SQL Server MVP
>