445,812 Members | 1,288 Online Need help? Post your question and get tips & solutions from a community of 445,812 IT Pros & Developers. It's quick & easy.

# SQL Float Operation with Round() fails completely

 P: n/a 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 Replies

 P: n/a 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" wrote in message news:2b**************************@posting.google.c om... 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

 P: n/a "Peter Scheurer" wrote in message news:2b**************************@posting.google.c om... 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

 P: n/a 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" 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

 P: n/a 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

 P: n/a 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 discussion thread is closed

Replies have been disabled for this discussion. 