convert float to char (ASE 11.9.2 , ASE 11.9.3)

declare @n float
select @n = 12.001
select convert(varchar(20), @n)
go
isql returned
 --------------------
 12.000999999999999

Why? MS SQL 6.5 returns
12.001

Thanks
Ivan Rubinov





0
Ivan
2/11/2000 12:47:10 PM
sybase.sqlserver.general 4269 articles. 0 followers. Follow

1 Replies
1247 Views

Similar Articles

[PageSpeed] 17

This is a multi-part message in MIME format.
--------------B8833FC9B364722D034F7974
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

First, when converting from an imprecise numeric (FLOAT, REAL) you
should be using
the str() function, rather than convert.

The floating point datatype cannot store 12.001 exactly.
The convert is displaying the value that is actually stored (or rather,
the closest representation it
can fit into a varchar(20)).

The MS SQL 6.5 client appears to be rounding the displayed value.  If
you use the str function
and specify 3 places of precision, it will also display 12.001.

Attached is my writeup on the floating point datatype that explains many
of the commonly seen issues.

-bret


Ivan Rubinov wrote:

> declare @n float
> select @n = 12.001
> select convert(varchar(20), @n)
> go
> isql returned
>  --------------------
>  12.000999999999999
>
> Why? MS SQL 6.5 returns
> 12.001
>
> Thanks
> Ivan Rubinov

--------------B8833FC9B364722D034F7974
Content-Type: text/plain; charset=us-ascii;
 name="floatingpoint.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline;
 filename="floatingpoint.txt"

The floating point datatype was designed to be able to hold a wide range of
values and allow fairly rapid arithmatic operations on them, at the expense
of absolute accuracy.

The exact nature of the imprecision inherent in floating point
datatypes is a source of much  confusion to many people.  This paper attempts
to explain that nature of the imprecision.  Some aspects of actual floating
point implementation have been simplified or ignored (such as the final 
two's-complement representation).
 
It should be noted that Sybase did not develop the floating point datatype;
it is a widely used IEEE standard.  C or C++ programs on the same platform as
SQL Server will demonstrate the similar floating point behavior [see Question
5 in the Q&A section below].  There are two common standard types of floating 
point numbers: 4-byte reals and 8-byte doubles.

Reals and doubles store values in a similar format: 1 sign bit, <x> exponent
bits, and <y> mantissa bits.  The only difference is that reals use smaller
exponents and mantissas.

-------------------------------------------------------------------------
        According to the IEEE standard for floating point
Real datatypes  have a 23 bit mantissa* and a 9 bit exponent (total 32).
Double datatypes have a 53 bit mantissa* and an 11 bit exponent (total 64)

Some platforms use different standards.  A double on a VAX, for instance, uses
a 56-bit mantissa*, 8-bit exponent, and 1 sign bit (total 64)

*The right-most bit is implicit.  It is not actually stored as it is always on.
-------------------------------------------------------------------------

The mantissa is a binary representation of the number, each bit representing
a power of two.  There is an additional implicit bit at the beginning 
(right hand side) of the number, which is always on.  

The exponent indicates a power of two that is used to multiply (or shift)
the mantissa to represent larger or smaller values.  The first bit of the
mantissa represents the value 2^<exponent>, the second bit 2^<exponent-1>, etc.

After the mantissa bits needed to represent the whole number part of the 
number have been used, the fractional part of the number is represented with 
the remaining bits (if any), which have values of negative powers of two..

For the sake of a simple demonstration, imagine an even smaller floating
point format with a 12 bits [including one implicit] mantissa, one sign bit, 
and 4  exponent bits (total 16).   

#  [1]###########  ####
^   ^ ^            ^
|   | |            Exponent
|   | Mantissa
|   Virtual bit of Mantissa (always on)
Sign bit

To represent a number determine "is the number positive or negative?".
Set the sign bit if it is negative.  Then determine "what is the smallest power 
of 2 that is larger than the number?".  Subtract one from that power to 
find the exponent of the implicit bit in the mantissa.  Store that
exponent in the exponent field.  Subtract the value of the implicit bit
(2^exponent) from the number.  Determine if 2^(exponent-1) is larger than the 
remainder.  If so, set the next bit, subtract the value of that bit
from the number, and determine if 2^(exponent-2) is larger than the remainder.
Repeat until you run out of mantissa bits. 


For instance, to represent 123.3:

The number is positive, so the sign bit is set to 0:

0  [1]###########  ####

The smallest power of 2 that is larger than 123.3 is 128, or 2^7,
so the exponent is 7-1, or 6.  2^6 is 64, the value of the implicit mantissa bit

0  [1]###########  0110   

123.3 - 64 is 59.3.  2^5 is 32, which is smaller than 59.3, so the next bit is set

0  [1]1########## 0110

59.3-32 = 27.3.  2^4 is 16, which is smaller than 27.3, so the next bit is set

0  [1]11######### 0110

27.3 - 16 = 11.3.  2^3 is 8, which is smaller than 11.3, so the next bit is set

0  [1]111######## 0110

11.3 - 8 = 3.3.  2^2 is 4, which is larger than 3.3, so the next bit is not set

0  [1]1110####### 0110

3.3 - 0 = 3.3.  @^1 is 2, which is smaller than 3.3, so the next bit is set

0  [1]11101###### 0110

3.3 - 2 = 1.3.  2^0 is 1, which is smaller than 1.3, so the next bit is set

0  [1]111011##### 0110

1.3 - 1 = 0.3.  2^-1 is 0.5, which is larger than 0.3, so the next bit is not set

0  [1]1110110#### 0110

0.3 - 0 = 0.3.  2^-2 is 0.25, which is smaller than 0.3, so the next bit is set

0  [1]11101101### 0110

0.3 - 0.25 = 0.05.  2^-3 is 0.125, which is larger than 0.05, so the next bit is not set

0  [1]111011010## 0110

0.05 - 0 = 0.05.  2^-4 is 0.06125, which is larger then 0.05, so the next bit is not set

0  [1]1110110100# 0110

0.05 - 0 = 0.05.  2^-5 is 0.030625, which is smaller than 0.05, so the next bit is set

0  [1]11101101001 0110

This represents the actual value

64 + 32 + 16 + 8 + 2 + 1 + 0.25 + 0.030625 = 123.280625

123.3 - 123.280625 is an error of 0.019375

It may be possible to reduce the error by rounding up to the next larger
number that can be represented (ie, add 2^-5).  This representation would
be

 0 [1]11101101001 0110
+               1
 0 [1]11101101010 0110
 
 64 + 32 + 16 + 8 + 2 + 1 + 0.25 + 0.06125 = 123.31125
 
 123.3 - 123.31125 is an error of -0.01125
 
 This is a smaller error, so the representation is rounded to
 
 0 [1]11101101010 0110 (123.31125) as the final representation.
 
 
 The standard "real" and "double" floating point formats work exactly the
 same, except they have wider mantiassas that reduce the magnitude of the
 potential error, and wider exponents that extend the posssible range
 of the number.
 
          Some frequently asked questions:

---------------------------------------------------------------------- 
 1) Why doesn't round() work right with floating point? There is
 garbage in the sixth decimal place when I round a floating point, as in:
 
 declare @x real
 select @x = 123.31
 select @x = round(@x,1)
 select @x
 go
 -------------
    123.300003
 
 A:  The decimal rounded value of 123.31 is 123.3, but the real datatype
 cannot store 123.3 exactly.  The garbage is due to the inherent imprecision
 of the floating point format.  There is also a display issue:  isql by default
 displays floats with 6 digits after the decimal point.  Some front end 
 programs are more intelligent about this: they know how many digits of the
 number will be accurate and truncate or round the display at that point.  You 
 should use the TSQL str() function for better control over the display of 
 floating point data [str() is documented under "String Functions" in the 
 manuals.]  For instance, we rounded to 1 decimal place, so there is no need 
 to display past one decimal place:
 
 select str(@x,8,1)
 go
 -------- 
    123.3 

(1 row affected)

 
 -----------------------------------------------------------------------
 2) So just how inaccurate are reals and doubles?
 
 4-byte reals can store a number with a maximum error of
 (the number) * (2^-23)
 
 8-byte doubles can store a number with a maximum error of
 (the number) * (2^-53)
 
As a rule of thumb, this means you can expect the
first 7 digits of a real to be correct, and the first 15 digits of 
a double to be correct.  After that, you may start seeing signs of 
inaccuracy or "garbage".
 

------------------------------------------------------------------------ 
3)  When I declare a column or variable to be of type float, there is an
 optional [(precision)] specification.  What effect does this have?
 
If precision is < 16, the server will use a 4-byte real.
If the precision is >= 16, the server will use a 8-byte double.
You can also explicitly tell the server to use type "real" or type 
"double precision".  If you don't specify a precision and use "float",
the server will default to a double.

The (precision) specification otherwise has no effect.  The syntax may
seem somewhat pointless, but it is allowed for compatibility with DDL
developed for other systems that interpret (precision) differently.

------------------------------------------------------------------------

4)  So floating point only has problems storing fractions, right?

Nope.  You can see problems in whole numbers, too.

For instance, reals have 23 bits in the mantissa, so they will have problems
with numbers that require more than 23 bits to represent correctly.

The smallest value we see this for is 2^24+1.  Reals can store 2^24th
with no problem (it only requires the implicit bit being on, and the 
exponent set to 24, all the other mantissa bits are zeroed), but 2^24+1 
requires 22 zero bits and a final one bit following the implicit bit 
(24 bits total, only 23 available).
 

1> select  power(2,24)-1, power(2,24), power(2,24)+1
2> go
 ----------- ----------- ----------- 
    16777215    16777216    16777217 
(1 row affected)
 
1> create table float_test (x real, y real, z real)
2> go
1> insert float_test values (power(2,24)-1, power(2,24), power(2,24)+1)   
2> go
(1 row affected)
1> select * from float_test
2> go
 x                    y                    z                    
 -------------------- -------------------- -------------------- 
      16777215.000000      16777216.000000      16777216.000000 
(1 row affected)

 
Note that the closest representation of 2^24+1 in a real is equal to 2^24

-----------------------------------------------------------------------

5)  I don't see this behavior in my c/c++ program.  What's up?

You probably aren't looking hard enough.  In general, printf() in c and
cout in c++ do not print out with enough precision to show the problems.
The imprecision is hidden by rounding done by the display process.

Try specifying a higher precision, as in these two sample programs:

=========================================
For c:
=========================================
main()
/* Program to demonstrate floating point imprecision */
{
float r;
double d ;
r = 123.3;
d = 123.3;

printf("As a %d-byte float (real):    123.3 is %48.24f \n", sizeof(r),r); 
printf("As a %d-byte double:  123.3 is %48.24f \n", sizeof(d),d);
 
}

Sample output on Solaris 2.5:

alliance1{bret}125: a.out
As a 4-byte real:    123.3 is                     123.300003051757812500000000 
As a 8-byte double:  123.3 is                     123.299999999999997157829057 
 

=========================================
For c++:
=========================================
 #include <iostream.h>
 #include <iomanip.h>
 
 main()
 -- Program to demonstrate floating point inaccuracy.
 {
 
 int precision;
 float y;
 y = 123.3;
        cout << "123.3 as a float printed with increasing precision" << endl;
        cout << "-------------------------------------------------------------" << endl;
        for (precision = 1; precision < 30; precision++)
        {
        cout.precision(precision);
        cout <<precision << "   " << y << endl;
        }
 double x;
 x = 123.3;
        cout << endl;
        cout << "123.3 as a double, printed with increasing precision" << endl;
        cout << "-------------------------------------------------------------" << endl;
 
        for (precision = 1; precision < 30; precision++)
        {
        cout.precision(precision);
        cout <<precision << "   " << x << endl;
        }
} 
 
Sample output on Solaris 2.5:
 
alliance1{bret}140: a.out
123.3 as a float printed with increasing precision
-------------------------------------------------------------
1   1e+02
2   1.2e+02
3   123
4   123.3
5   123.3
6   123.3
7   123.3
8   123.3
9   123.300003
10   123.3000031
11   123.30000305
12   123.300003052
13   123.3000030518
14   123.30000305176
15   123.300003051758
16   123.3000030517578
17   123.30000305175781
18   123.300003051757812
19   123.3000030517578125
20   123.3000030517578125
21   123.3000030517578125
22   123.3000030517578125
23   123.3000030517578125
24   123.3000030517578125
25   123.3000030517578125
26   123.3000030517578125
27   123.3000030517578125
28   123.3000030517578125
29   123.3000030517578125
 
123.3 as a double, printed with increasing precision
-------------------------------------------------------------
1   1e+02
2   1.2e+02
3   123
4   123.3
5   123.3
6   123.3
7   123.3
8   123.3
9   123.3
10   123.3
11   123.3
12   123.3
13   123.3
14   123.3
15   123.3
16   123.3
17   123.3
18   123.299999999999997
19   123.2999999999999972
20   123.29999999999999716
21   123.299999999999997158
22   123.2999999999999971578
23   123.29999999999999715783
24   123.299999999999997157829
25   123.2999999999999971578291
26   123.29999999999999715782906
27   123.299999999999997157829057
28   123.299999999999997157829057
29   123.29999999999999715782905696

---------------------------------------------------------------------
6)  Where can I find more information on floating points?

Many books on assembly language programming go into great detail.

A search on the World Wide Web for keywords "IEEE" and "floating"
will provide many documents of interest.

 
--------------B8833FC9B364722D034F7974--

0
Bret
2/11/2000 4:02:19 PM
Reply:

Similar Artilces:

ASE 11.0.3.3 and ASE 11.9.2 on same box
Hi evereyone, I would like to install 2 ASE servers with different versions on same linux box. Is this possible? Here is what I tried to do: I have a working ASE 11.9.2 on linux box tried to install ASE 11.0.3.3 on same box in different directory and it gave me an error. Wondering if this ispossible at all. Appreciate any help. TIA ar The 11.0.3.3 rpm tries to uninstall the 11.9.2 package. I shutdown the 11.9.2 server before install 11.0.3.3 and I do not keep the both versions running. The variables SYBASE, LD_LIBRARY_PATH ,PATH must be correctly set to point the...

replicate ASE 11.0.3 via R.S. 11.5 to ASE 11.9.2
Has any one successfully replicated from 11.0.3 using Rep Server 11.5 to ASE 11.9.2 using ltm's? We are running on solaris 2.6 using veritas filesystem and keep getting stack traces (this shuts down the connection). I would like to know if anyone else has replicated using these versions or something similar. Thanks. kboniface@worldres.com Yes, I have done this. No specific problems. HTH, Rob -- ---------------------------------------------------------------------- Rob Verschoor Certified Sybase ASE 11.5 Administrator (Professional) Certified DBA/Performance...

Upgrade 11.0.3.3 to ASE 11.9.2 with RepServer 11.5 and LTMs
I am preparing to upgrade our production servers (11.0.3.3) using RepServer 11.5 with LTMs. My understanding is that the ASE 11.9.2 has RepAgents build in as Open Server applications. Are there any issues with upgrading that I need to be aware of? Where would I look? I would assume that I don't need to run LTMs anymore, but I need to ensure no loss of replication to the warm standby. Any advice? Take care. JCS ASE 11.5+ has RepAgent built in as internal server threads (which has nothing to do with OpenServer). When upgrading, keep the LTM's and upgrade to RS 11.5 / ...

Download Database from ASE 11.9.2 to ASE 11.0.2 ?
Dear All: We have a Sybase ASE11.9.2 on Window NT, and we already run some application on it about 6 months. Since the third party software we use cann't support ASE11.9.2, we must use ASE 11.0.2. Can we just backup the database of ASE11.9.2 then restore the backup database to ASE11.0.2 ? Has it any problem ? Or, We must do following procedure. 1) bcp out the data from ASE11.9.2 2) create the database on ASE11.0.2 3) bcp in the 1) data to ASE11.0.2 Can anyone tell me?? Teresa Young Teresa, The problem with dump and load is that the log format in 11.9.2 is much diff...

Upgrade from 11.0.3.2 to ASE 11.9.2
I've seen different postings claiming that 11.0.3 can directly upgrade to 11.9.2. However, the release bulletin for Sybase ASE 11.9.2 (Doc ID = 72410-01-1192-02) says that "You can upgrade 11.9.2 only from 10.0.2.7, 11.0.3.3, 11.5 and later). We are currently using 11.0.3.2. Does it mean that I will have to upgrade our server to 11.0.3.3 (from 11.0.3.2) before I can migrate to ASE 11.9.2? I will appreciate if someone clarify this. Thanks. CGS tcgs@hotmail.com CGS wrote: > > I've seen different postings claiming that 11.0.3 can directly upgrade...

Problem in Installating Monitor Server 11.9.2 on ASE 11.9.2.
Hi, I was tryint to install SQL monitor server 11.9.2 on ASE 11.9.2 on HP-UX 11.0 9000/800. It gives the following error """""""""""""""""""""""" Nov 14 12:28:18 2000: DB-Library Version: Nov 14 12:28:18 2000: Sybase DB-Library/11.1.1/P-EBF8022/hp800/HP-UX 10.01/1/OPT/Fri Jun 26 15:04:39 1998 Nov 14 12:28:18 2000: Open Server Version: Nov 14 12:28:18 2000: Sybase Server-Library/11.1.1/P-EBF8022/hp800/HP-UX 10.01/1/OPT/Fri Jun 26 13:53:53 1998 Nov 14 12:2...

upgrade ASE from 11.0.3.3 to 11.9.2 on AIX
Hi, We are planning to upgrade our SQL Server 11.0.3.3 servers to ASE 11.9.2. We have 6 production servers on one UNIX box and (almost) corresponding test servers on another UNIX box. We have little new development, so we should have enough time to test things out first on the TEST platform. I am trying to decide between upgrading the servers in place or doing new installs and migrating the data over to the new servers. The upgrades in place would be a lot easier on me and the UNIX support people, but I am not sure how much more difficult fallback would be. If we needed to fall...

Cannot drop foreign key constraint in ASE 11.9.2
Hi I need to drop foreign key constraint and drop referenced table, then create another table-parent to be referenced . The problem is that it doesn't allow to drop table because of FKey constraint. When I try to drop the FKey constraint - it doesn't allow this and doesn't give an error message. This problem I experience on production server and this should be fixed urgently but seems like nothing I've tried helps to solve it. I'm using ASE 11.9.2 on NT4.0. If anybody has some ideas it would be very appreciated. Thank you. Ninel <<<< I need to drop ...

Upgrading ASE 11.9.2 to ASE 12.5 on Aix 4.3.3
After migration from ASE 11.9.2 to ASE 12.5, the following error appears when I try to execute a strore procedure : Size of row in the worktable for grouped aggregate, 16542 bytes, exceeds the maximum row size (1962 bytes). Cannot run this query (return status = -6). Is it a bug on ASE 12.5? Why could I run correctly this procedure on the old version? Fabien DUBOIS Fabien DUBOIS wrote: > > After migration from ASE 11.9.2 to ASE 12.5, the following error appears > when I try to execute a strore procedure : > > Size of row in the worktable for grouped aggr...

SQLRemote Problem with 7.0.2-1 and 6.0.2-1 on Linux [ASE 11.9.2-3 and ASE 12.5 eval]
Hi all I got the following problems using sql-remote. on ASE 12.5 eval: I got the following RPMs installed on RedHat Linux 7.2 - Kernel 2.4.17 - glibc-2.2.4 sybase-ase-12.5-3.i386.rpm sybase-common-12.5-3.i386.rpm sybase-openclient-12.5-3.i386.rpm sybase-sqlremote-7.0.2-1.i386.rpm After the execution of ssxtract or ssremote I got an error like: ../ssxtract: /opt/sybase-12.5/SQLRemote/lib/libsstasks7_r.so: version `libdbtasks7_r.so' not found (required by ./ssxtract) The file libsstasks7_r.so exists and the path is correctly specified in /etc/ld.so.conf. There'...

11.9.2.3 to 11.9.2.4
We are currently running version 11.9.2.3 on HP/UX 10.2. I would like to upgrade to 11.9.2.4 but another DBA has advised me that, if we do this, we will have to upgrade the client software on all the client machines. That sounds strange to me. We are currently running 11.1 clients on our PC's. Is anyone aware of a required client upgrade when going to 11.9.2.4? If so, are there any work arounds that would avoid having to upgrade all client PC's? Hi Robert. Moving from 11.9.2.3 to 11.9.2.4 should not impact your clients and would not require updating any software on th...

Transferring database ASE 11.9.2 -> ASE 11.5
Hello, I need to transfer a database from a ASE 11.9.2 server to a ASE 11.5 server. (Servers have the same specs and run Windows NT 4.0). The database doesn't have many objects such as tables, users, Views, SP's etc. etc. It has a 400 MB device for data and 100 MB Device for the log, with standard segmentation. It's relatively small. I understand from this newsgroup that dumping on a newer release and loading on an older release is not an option. Now I figure I have to do the following: - Create a data and a log device with similar names and sizes on the ASE 11.5 se...

ASE 12 running with ASE 11.9.2 and RS 11.5
Hi, I have a customer that asks the following question: "Are there any issues running Sybase 32 and 64 bit applications on the same box? What about replication? Is it possible for us to upgrade to 12 repserver and dataserver on our central complex and continue to run 11.5 repserver/11.9.2 dataserver at remotes?" I would think that this configuration would be ok, but I thought I'd better verify it. Thanks, Pat Proft Alliance Support Pat Proft wrote: > Hi, > I have a customer that asks the following question:...

significant difference between 11.9.2 and 11.9.2.3
Hi, we have two database servers. one with Adaptive Server 11.9.2, the other with Adaptive Server 11.9.2.3. On both are exactly the same databases. In one table there is a char(1) column bound to a user datatype including a rule, which allows only a "Y" or a "N". The column is allowed to have NULL values If I try to insert a row under 11.9.2 nothing happens, I can insert a NULL value in this column. Under 11.9.2.3 I get the error 522 "A column insert or update conflicts with a rule bound to the column ..... " I cannot explain that for myself...

Web resources about - convert float to char (ASE 11.9.2 , ASE 11.9.3) - sybase.sqlserver.general

List of converts to Buddhism - Wikipedia, the free encyclopedia
Ajahn Brahm (born 1951), Religious teacher and student of the late Thai monk Ajahn Chah . Currently he is the abbot of the Bodhinyana Monastery, ...

Facebook Timeline For Pages: 8 Million Converts, 29 Million To Go
... reach to 98 percent of users who liked its page, as well as to double its engagement rate, during a 28-day period. Dr. Pepper hasn’t yet converted ...

Facebook stock roundup: Execs offload more stock, directors convert shares
... Bowles, the former co-chair of the now-defunct National Commission on Fiscal Responsibility and Reform, and Reed Hastings, Netflix CEO, converted ...

How to Convert Leads Into Buying Customers - The 9 Step Sales Process EVERYONE Should Use
... School of Business in Toronto on the topics of Sales and Entrepreneurship. I shared with them the sales process that I find How to Convert Leads ...

Bitcoin : Should I convert my savings to Bitcoin?
Answer (1 of 7): You should think of this question as "Should I invest all my savings in Bitcoin?", and not "Should I have my savings denominated ...

Coordinates - Calculate and Convert a Position on the App Store on iTunes
Get Coordinates - Calculate and Convert a Position on the App Store. See screenshots and ratings, and read customer reviews.

Radical plan to convert Domain Stadium into a wave park
The man behind a radical plan to convert Domain Stadium into an artificial wave park, says it will become &nbsp;a sporting spectacle for wave-starved ...

WWE diva converts to Christianity, confesses to a life of sin
Stunning wrestling diva Taryn Terrell has shared a dramatic knock-down testimony of conversion from life in the ring to life with Christ.

Microsoft converts more customers into testers with Office Insider preview program
Microsoft today launched a new preview program for consumers who subscribe to Office 365 that will give them a sneak peak at new features slated ...

'Water Bug' Robot Digests Pollution, Converts it to Electricity
The Row-Bot was capable of generating more energy than it needed.

Resources last updated: 11/23/2015 6:48:21 AM