473,373 Members | 1,117 Online

# SQL Float Operation with Round() fails completely

Hi,

we found some strange behavior when operating with floats and round().
The following simplified statement reproduces the problem.

select 6.56 - round(convert(float, 6.56), 2)
from sysusers where name = 'public';
===========
-8.88178419

In "real world", the floats are not literals, but float fields in tables.
I could figure, that 6.56 - 6.56 will result in something like 0.00001,
but -8.88178419 is beyond my imagination.
The same statement without round results in 0.

Has anyone any clue on this?
Regards,
Peter
Jul 20 '05 #1
5 12585
Somehow you're truncating the result, and losing the E-16 (which means
*10^-16). Where are you retrieving your results? I doubt query analyzer
would do this..

select 6.56 - round(convert(float, 6.56), 2)

-----------------------------------------------------
-8.8817841970012523E-16

BTW, you can add E or E0 to the end instead of convert(float, constant)

select 6.56 - round(6.56e, 2)

-----------------------------------------------------
-8.8817841970012523E-16

6.56 is a decimal constant, and 6.56e is a float. Rounding apparently rounds
up, even when the result is further from the exact number.

select 6.56e union all
select round(6.56e,2)

-----------------------------------------------------
6.5599999999999996
6.5600000000000005

If your client app can't deal with floats properly, perhaps you could pass
back a varchar:

select cast(cast(6.56e as decimal(9,2)) as varchar(10))

----------
6.56

Actually, the cast as decimal shouldn't be necessary; casting as varchar
automatically rounds:

select cast(6.56e as varchar(10))

----------
6.56

Actually, it rounds a lot more than I would have thought

select cast(6.560001e as varchar(54))

------------------------------------------------------
6.56

If I want to see more digits, it seems I have to cast as decimal...

select cast(cast(6.56000001e as decimal(9,8)) as varchar(10))

----------
6.56000001

But be careful:

select cast(cast(6.56000001e as decimal(9,8)) as varchar(9))
Server: Msg 8115, Level 16, State 5, Line 1
Arithmetic overflow error converting numeric to data type varchar.

Return no more than 16 digits (15 decimals) for a number 0..10 and it should
be rounded properly.

select cast(cast(6.56e as decimal(18,17)) as varchar(19))

-------------------
6.55999999999999960

select cast(cast(6.56e as decimal(18,15)) as varchar(19))

-------------------
6.560000000000000

--

Incidentally, I wonder why QA sees the need to return a 53-character result
for a float. It should never need to be over 18 decimals + 1 for the decimal
point + 5 for the exponent = 24.

Hmm, BOL says this about float data type:

The MicrosoftŽ SQL ServerT float[(n)] data type conforms to the SQL-92
standard for all values of n from 1 to 53. The synonym for double precision
is float(53).

So it's treating the precision (53) as the the width... weird.

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

"Peter Scheurer" <sc******@gmx.com> wrote in message
Hi,

we found some strange behavior when operating with floats and round().
The following simplified statement reproduces the problem.

select 6.56 - round(convert(float, 6.56), 2)
from sysusers where name = 'public';
===========
-8.88178419

In "real world", the floats are not literals, but float fields in tables.
I could figure, that 6.56 - 6.56 will result in something like 0.00001,
but -8.88178419 is beyond my imagination.
The same statement without round results in 0.

Has anyone any clue on this?
Regards,
Peter
Jul 20 '05 #2

"Peter Scheurer" <sc******@gmx.com> wrote in message
Hi,

we found some strange behavior when operating with floats and round().
The following simplified statement reproduces the problem.

select 6.56 - round(convert(float, 6.56), 2)
from sysusers where name = 'public';
===========
-8.88178419

In "real world", the floats are not literals, but float fields in tables.
I could figure, that 6.56 - 6.56 will result in something like 0.00001,
but -8.88178419 is beyond my imagination.
The same statement without round results in 0.

Has anyone any clue on this?
Regards,
Peter

The result of the query above is this (on my system):

-8.8817841970012523E-16

Note the E-16 at the end - that's the number before the E multiplied by 10
to the power of minus 16, which is a very small number indeed. Since float
calculations are not precise, a very small difference is not unusual.
Compare the result of this query:

select cast(1.01 as float)-cast(1.0 as float)

1.0000000000000009E-2

This is roughly 1E-2, ie. 0.01.

Simon
Jul 20 '05 #3
I'd like to add that decimal calculations aren't precise either, if you are
calculating with numbers that can't be represented 100% accurately in a
decimal form, eg 1.0/3.0 or 1.0/7.0. Binary calculations are precise on
numbers that are representable accurately in binary in the number of bits
provided in the mantissa of the FLOAT (53 bits) or REAL (24 bits), eg. any
number which can be represented as SUM(POWER(2,x)) where x is a range of
integers such that MAX(x)-MIN(x) <= 52, for FLOAT. Decimals are often not
accurately represented in binary (FLOAT/REAL), because 1/5 is not accurately
representable in binary. The Decimal datatypes change the exponent to a
power of 10, and make the mantissa an integer of 4, 8, 12 or 16 bytes, so
that all decimal numbers of up to 38 digits can be accurately represented.

SELECT 1.-2./3.-1./3.

-----------
.000001

(wow, only 6 decimals by default for a decimal?)

SELECT 1 - 2/3e0 - 1/3e0

-----------------------------------------------------
5.5511151231257827E-17
SELECT 1.0/7.0*7.0

-------------
.9999990

--SELECT 1.0/7.0*7.0

declare @decimals int
declare @sql varchar(80)
set @decimals=0
while @decimals<37
begin
set @sql='print cast(1.'+replicate('0',@decimals)+' as varbinary)'
exec(@sql)
set @decimals = @decimals + 1
end

Some output:
0x0100000101000000 -- 1. is represented as 1 * 10^(1-1)
0x020100010A000000 -- 1.0 is 10 (0x0A) * 10^(2-1)
0x0302000164000000 -- 1.00 is 100 (0x64) * 10^(3-1)... etc
....
0x0B0A000100E40B5402000000
....
0x15140001000010632D5EC76B05000000
....
0x1E1D0001000000A0CA17726DAE0F1E4301000000
....
0x2524000100000000109F4BB31507C97BCE97C000
"Simon Hayes" <sq*@hayes.ch> wrote in message
news:40********@news.bluewin.ch...

[...] Since float
calculations are not precise, a very small difference is not unusual.
Compare the result of this query:

select cast(1.01 as float)-cast(1.0 as float)

1.0000000000000009E-2

This is roughly 1E-2, ie. 0.01.

Simon

Jul 20 '05 #4
Thank you Simon and Aaron,

you were both right:
I tried the statement with query analyzer and I got the correct and
complete result.
It is sqltalk - a tool from Centura Developer, that does the wrong job
when converting floats for viewing in the result window.

Thank you for helping me.
Peter

The result of the query above is this (on my system):

-8.8817841970012523E-16

Note the E-16 at the end - that's the number before the E multiplied by 10
to the power of minus 16, which is a very small number indeed. Since float
calculations are not precise, a very small difference is not unusual.
Compare the result of this query:

select cast(1.01 as float)-cast(1.0 as float)

1.0000000000000009E-2

This is roughly 1E-2, ie. 0.01.

Simon

Jul 20 '05 #5
Thank you Simon and Aaron,

you were both right:
I tried the statement with query analyzer and I got the correct and
complete result.
It is sqltalk - a tool from Centura Developer, that does the wrong job
when converting floats for viewing in the result window.

Thank you for helping me.
Peter

The result of the query above is this (on my system):

-8.8817841970012523E-16

Note the E-16 at the end - that's the number before the E multiplied by 10
to the power of minus 16, which is a very small number indeed. Since float
calculations are not precise, a very small difference is not unusual.
Compare the result of this query:

select cast(1.01 as float)-cast(1.0 as float)

1.0000000000000009E-2

This is roughly 1E-2, ie. 0.01.

Simon

Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

### Similar topics

 2 by: j vickroy | last post by: Howdy, I do not understand the following behavior for: PythonWin 2.3.2 (#49, Oct 2 2003, 20:02:00) on win32. >>> >>> float('0.0') 0.0 >>> row = ('0.0', '1.0', None) 9 by: Vineet Jain | last post by: int('2.1') does not work while int(float('2.1')) does. If int can covert a float object then there is no reason why a float string should not be converted too. When I do int('2.1') I get the... 9 by: Marc Schellens | last post by: My compiler warns about assignment to int form float/double. Will it nevertheless do what I expect? Are there caveeats/pits? (Apart from the range and the precision of course) What would be the... 5 by: Pat | last post by: Give two double-typed variable X and Y. If (X==Y) is true, then how about the following results: (float(X) > float(Y))? (float(X) < float(Y))? (float(X) >= float(Y))? ( X > float(Y) )? ( X... 9 by: Domel | last post by: 1 Jak zaokrąglić float'a tak żeby jeżeli część dziesiętna była większa lub równa od 0.5 to był on zaokrąglany w górę, jeżeli mniejsza to w dół np 1.6 po zaokr 2.0 1.2 po zaokr 1 prosiłbym o ile... 6 by: Dave win | last post by: Hi all: I'm confused with the expression "(float *())". Book says that this is a cast. But, I have no idea of this expr. why could this expr ignore the variable??? Thanx!!! 3 by: Terje Barman | last post by: Hi! I have sumarized float values and I'd like to round the sum to a certain precision. Say I have 1.234 and want it rounded to 1.23. Which method can I use? Terje 19 by: morc | last post by: hey, I have float values that look something like this when they are printed: 6.0E-4 7.0E-4 I don't want them to be like this I want them to be normalized with 4 decimal places. 116 by: Dilip | last post by: Recently in our code, I ran into a situation where were stuffing a float inside a double. The precision was extended automatically because of that. To make a long story short, this caused... 0 by: Faith0G | last post by: I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be... 0 by: isladogs | last post by: The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former... 0 by: ryjfgjl | last post by: In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import... 0 by: taylorcarr | last post by: A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,... 0 by: ryjfgjl | last post by: If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming... 0 by: emmanuelkatto | last post by: Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel 0 by: BarryA | last post by: What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress... 1 by: nemocccc | last post by: hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions? 1 by: Sonnysonu | last post by: This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...