SQL Server Version 7.00.623 - Numeric Input Problem
| Author |
Message |
|
Kevin Philbric
#1 / 5
|
 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 |
|
 |
|
BP Margoli
#2 / 5
|
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 |
|
|
|
RH
#3 / 5
|
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 |
|
|
|
BP Margoli
#4 / 5
|
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 |
|
|
|
User
#5 / 5
|
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 |
|
| |
|
