SQL Server Version 7.00.623 - Numeric Input Problem 
Author Message
 SQL Server Version 7.00.623 - Numeric Input Problem
There are several fields in the database that are defined
as Number(22, 15).  When a number that has fewer
significant digits is inserted into that field, the number
gets rounded to a number that's smaller by a hair
(actually, .000000000000001 - .00000000000010)

Example:  If a value of 2.03 is inserted into Field01, the
value of that field is shown as 2.0299999999999998.

This happens occasionally, not everytime.  Is anyone aware
of a patch that addresses this problem?

Thanks in advance.

--Kevin



Mon, 23 Feb 2004 01:16:15 GMT
 SQL Server Version 7.00.623 - Numeric Input Problem

Kevin,

I can't check out your assertion directly because I'm running SS7 w/SP3
(7.00.961), but it does sound to me as if you are somewhere converting the
value to a real. In fact, from the fact that you say Number rather than
Numeric, I suspect that you are looking at the value via Microsoft Access.

I ran the following on SS7 w/SP3, in Query Analyzer:




and SQL Server returned:

------------------------
2.030000000000000

I'm not an expert in Access (not to mention that there are those who would
claim that I'm not an expert in SQL Server), but I suspect that the problem
is with how Access is presenting the data rather than how SQL Server is
storing it. Can you bypass Access, and use Query Analyzer directly to query
your data, and see what you get?

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> There are several fields in the database that are defined
> as Number(22, 15).  When a number that has fewer
> significant digits is inserted into that field, the number
> gets rounded to a number that's smaller by a hair
> (actually, .000000000000001 - .00000000000010)

> Example:  If a value of 2.03 is inserted into Field01, the
> value of that field is shown as 2.0299999999999998.

> This happens occasionally, not everytime.  Is anyone aware
> of a patch that addresses this problem?

> Thanks in advance.

> --Kevin



Mon, 23 Feb 2004 08:16:27 GMT
 SQL Server Version 7.00.623 - Numeric Input Problem
Kevin,
It seems that we have the same problem. I have a table, called TAX with a
field called tPercentage.
That field was a float and in the SQLDB one of the value's was 1,19 and when
I used query-analyser to get that value, it gave back 1,18888888888888889 or
1,19000000000000001.

So, I think it's a bug in SQLServer, I installed SP3, but that did not help.
I fixed it by making the type of the field : Decimal, Length 9, precision 18
and scale 3

Hope this helps

Ron




Quote:
> Kevin,

> I can't check out your assertion directly because I'm running SS7 w/SP3
> (7.00.961), but it does sound to me as if you are somewhere converting the
> value to a real. In fact, from the fact that you say Number rather than
> Numeric, I suspect that you are looking at the value via Microsoft Access.

> I ran the following on SS7 w/SP3, in Query Analyzer:




> and SQL Server returned:

> ------------------------
> 2.030000000000000

> I'm not an expert in Access (not to mention that there are those who would
> claim that I'm not an expert in SQL Server), but I suspect that the
problem
> is with how Access is presenting the data rather than how SQL Server is
> storing it. Can you bypass Access, and use Query Analyzer directly to
query
> your data, and see what you get?

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > There are several fields in the database that are defined
> > as Number(22, 15).  When a number that has fewer
> > significant digits is inserted into that field, the number
> > gets rounded to a number that's smaller by a hair
> > (actually, .000000000000001 - .00000000000010)

> > Example:  If a value of 2.03 is inserted into Field01, the
> > value of that field is shown as 2.0299999999999998.

> > This happens occasionally, not everytime.  Is anyone aware
> > of a patch that addresses this problem?

> > Thanks in advance.

> > --Kevin



Tue, 24 Feb 2004 23:03:03 GMT
 SQL Server Version 7.00.623 - Numeric Input Problem
Ron,

It is NOT a bug in SQL Server. Float is an approximate data type. Please
check out the section "Using decimal, float, and real Data" in the SQL
Server Books Online.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:

> Kevin,
> It seems that we have the same problem. I have a table, called TAX with a
> field called tPercentage.
> That field was a float and in the SQLDB one of the value's was 1,19 and
when
> I used query-analyser to get that value, it gave back 1,18888888888888889
or
> 1,19000000000000001.

> So, I think it's a bug in SQLServer, I installed SP3, but that did not
help.
> I fixed it by making the type of the field : Decimal, Length 9, precision
18
> and scale 3

> Hope this helps

> Ron




> > Kevin,

> > I can't check out your assertion directly because I'm running SS7 w/SP3
> > (7.00.961), but it does sound to me as if you are somewhere converting
the
> > value to a real. In fact, from the fact that you say Number rather than
> > Numeric, I suspect that you are looking at the value via Microsoft
Access.

> > I ran the following on SS7 w/SP3, in Query Analyzer:




> > and SQL Server returned:

> > ------------------------
> > 2.030000000000000

> > I'm not an expert in Access (not to mention that there are those who
would
> > claim that I'm not an expert in SQL Server), but I suspect that the
> problem
> > is with how Access is presenting the data rather than how SQL Server is
> > storing it. Can you bypass Access, and use Query Analyzer directly to
> query
> > your data, and see what you get?

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > There are several fields in the database that are defined
> > > as Number(22, 15).  When a number that has fewer
> > > significant digits is inserted into that field, the number
> > > gets rounded to a number that's smaller by a hair
> > > (actually, .000000000000001 - .00000000000010)

> > > Example:  If a value of 2.03 is inserted into Field01, the
> > > value of that field is shown as 2.0299999999999998.

> > > This happens occasionally, not everytime.  Is anyone aware
> > > of a patch that addresses this problem?

> > > Thanks in advance.

> > > --Kevin



Wed, 25 Feb 2004 08:14:09 GMT
 SQL Server Version 7.00.623 - Numeric Input Problem
Yes, effectively float is an appoximate datatype.
However, a more complete explanation is that almost all computers store data
in binary form.
Thus "binary" fractions can be represented exactly (up to a certain number
of digits) but "decimal" fractions cannot.

For instance in decimal (base ten)
1/10 = 0.1 is exact
1/3 = 0.33333 ... is not exact

On the other hand, if you had a trinary (base three) computer
1/3 = 0.1 which is exact

In a binary (base 2) computer
1/2 = 0.1
1/4 = 0.001
etc ...
and numbers which are integral linear combinations of 1, 1/2, 1/4, 1/8 etc
can be represented exactly - down to a certain precision


Quote:
> Ron,

> It is NOT a bug in SQL Server. Float is an approximate data type. Please
> check out the section "Using decimal, float, and real Data" in the SQL
> Server Books Online.

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.


> > Kevin,
> > It seems that we have the same problem. I have a table, called TAX with
a
> > field called tPercentage.
> > That field was a float and in the SQLDB one of the value's was 1,19 and
> when
> > I used query-analyser to get that value, it gave back
1,18888888888888889
> or
> > 1,19000000000000001.

> > So, I think it's a bug in SQLServer, I installed SP3, but that did not
> help.
> > I fixed it by making the type of the field : Decimal, Length 9,
precision
> 18
> > and scale 3

> > Hope this helps

> > Ron




> > > Kevin,

> > > I can't check out your assertion directly because I'm running SS7
w/SP3
> > > (7.00.961), but it does sound to me as if you are somewhere converting
> the
> > > value to a real. In fact, from the fact that you say Number rather
than
> > > Numeric, I suspect that you are looking at the value via Microsoft
> Access.

> > > I ran the following on SS7 w/SP3, in Query Analyzer:




> > > and SQL Server returned:

> > > ------------------------
> > > 2.030000000000000

> > > I'm not an expert in Access (not to mention that there are those who
> would
> > > claim that I'm not an expert in SQL Server), but I suspect that the
> > problem
> > > is with how Access is presenting the data rather than how SQL Server
is
> > > storing it. Can you bypass Access, and use Query Analyzer directly to
> > query
> > > your data, and see what you get?

> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.



> > > > There are several fields in the database that are defined
> > > > as Number(22, 15).  When a number that has fewer
> > > > significant digits is inserted into that field, the number
> > > > gets rounded to a number that's smaller by a hair
> > > > (actually, .000000000000001 - .00000000000010)

> > > > Example:  If a value of 2.03 is inserted into Field01, the
> > > > value of that field is shown as 2.0299999999999998.

> > > > This happens occasionally, not everytime.  Is anyone aware
> > > > of a patch that addresses this problem?

> > > > Thanks in advance.

> > > > --Kevin



Wed, 25 Feb 2004 09:01:39 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Query bug in SQL Server 7.00.623 !?

2. What is the servicepack of SQL 7.00.623

3. Anyone know what version of SQL Server 7 has this version#: 7.00.718

4. Is the 7.00.623 the latest release of SQL 7 ? Is that the comercial version ???

5. Parameterized Queries in DTS using SYBASE 11 Driver Version 3.01.00.00

6. Strange behaviour of is_member() in SQL-Server 2000 (Product Version 8.00.534, S

7. Strange behaviour of is_member() in SQL-Server 2000 (Product Version 8.00.534, S

8. BUGS SQL 7.0 ( build 7.00.623 ): Query Analyzer; bcp command line utility with SQL 4.21a

9. Help with 00:00:00:00 format

10. Bad Timestamp Format at 19 in 1974-08-02 00:00:00.00+01] in PostgresQL

11. SQL Executive thinks SQL 6.5 is version 0.00.000

12. ODBC version 2.573.3711.00 problems


 
Powered by phpBB® Forum Software