Asp Forum - Rounding: round(x,2) gives 25011.790000000001 for 25011.799999999999

ErikYkema

3/30/2007 2:57:00 PM

Hi,
I am looking at a float field in a SqlServer2000 table.
In the database I got the following value: 25011.799999999999.
I like to export this to flatfile using DTS as 25011.80 or 25011.8
(rounding on two decimals).

When rounding this using round(x, 2, 1), I get: 25011.790000000001 !

_Why is the round(x, 2, 1) behaving like this anyhow?_

Some samples:
select pcpc.Credit as c1 -- 25011.799999999999
, round(pcpc.Credit, 2, 1) as c2 -- 25011.790000000001
, round(pcpc.Credit, 2) as c3 -- 25011.799999999999
, cast(cast(pcpc.Credit * 100 as int) as float)/100 as c4 --
25011.799999999999
, convert(nchar(32), round(pcpc.Credit, 2), 0) as c5 --
25011.8
, cast(convert(nchar(32), round(pcpc.Credit, 2), 0) as float) as c6
-- 25011.799999999999
from FOO ppc

So a float just can't hold the value 25011.799999999999 exactly, and
the nearest binary representation is 25011.799999999999 (c6) or
25011.790000000001 (c2)? Is that it?

I think I'll stick with column c5 for my download per flatfile to my
datawarehouse.

Fot those interested: an oracle equivalent:
select pcpc.Credit as c1 -- 25011.8
, round(pcpc.Credit, 2) as c3 -- 25011.8
, to_char(pcpc.Credit, 'FM9999999999.99') -- 25011.8
from (select 25011.799999999999 as credit from dual) pcpc

Regards,
Erik Ykema

4 Answers

Jack Vamvas

3/30/2007 3:26:00 PM

Using ROUND(25011.799999999999,2) i get 25011.8

--

Jack Vamvas
___________________________________
Advertise your IT vacancies for free at - http://www.ITj...

"ErikYkema" <erik.ykema@gmail.com> wrote in message
news:1175266634.706928.31390@o5g2000hsb.googlegroups.com...
> Hi,
> I am looking at a float field in a SqlServer2000 table.
> In the database I got the following value: 25011.799999999999.
> I like to export this to flatfile using DTS as 25011.80 or 25011.8
> (rounding on two decimals).
>
> When rounding this using round(x, 2, 1), I get: 25011.790000000001 !
>
> _Why is the round(x, 2, 1) behaving like this anyhow?_
>
> Some samples:
> select pcpc.Credit as c1 -- 25011.799999999999
> , round(pcpc.Credit, 2, 1) as c2 -- 25011.790000000001
> , round(pcpc.Credit, 2) as c3 -- 25011.799999999999
> , cast(cast(pcpc.Credit * 100 as int) as float)/100 as c4 --
> 25011.799999999999
> , convert(nchar(32), round(pcpc.Credit, 2), 0) as c5 --
> 25011.8
> , cast(convert(nchar(32), round(pcpc.Credit, 2), 0) as float) as c6
> -- 25011.799999999999
> from FOO ppc
>
> So a float just can't hold the value 25011.799999999999 exactly, and
> the nearest binary representation is 25011.799999999999 (c6) or
> 25011.790000000001 (c2)? Is that it?
>
> I think I'll stick with column c5 for my download per flatfile to my
> datawarehouse.
>
> Fot those interested: an oracle equivalent:
> select pcpc.Credit as c1 -- 25011.8
> , round(pcpc.Credit, 2) as c3 -- 25011.8
> , to_char(pcpc.Credit, 'FM9999999999.99') -- 25011.8
> from (select 25011.799999999999 as credit from dual) pcpc
>
> Regards,
> Erik Ykema
>

TenKMan

3/30/2007 3:48:00 PM

"Jack Vamvas" <DEL_TO_REPLY@del.com> wrote in message
news:HISdnakeaPK9sZDbRVnygwA@bt.com...
> Using ROUND(25011.799999999999,2) i get 25011.8

How you "get" the result determines what you see. Using QA and the following
script, you will see no difference.

declare @test float
set @test = 25011.799999999999
select @test, ROUND(@test, 2)

Tom Cooper

3/30/2007 3:48:00 PM

Float datatype is incapable of representating some decimal values exactly.
So if you want an exact decimal value, use decimal (or numeric) datatype.
To see what I mean, run

Declare @f float
Set @f = 25011.799999999999
Select ROUND(@f,2) As FloatType , Round(Cast(@f As decimal(19, 8)), 2) As
DecimalType

Tom

"ErikYkema" <erik.ykema@gmail.com> wrote in message
news:1175266634.706928.31390@o5g2000hsb.googlegroups.com...
> Hi,
> I am looking at a float field in a SqlServer2000 table.
> In the database I got the following value: 25011.799999999999.
> I like to export this to flatfile using DTS as 25011.80 or 25011.8
> (rounding on two decimals).
>
> When rounding this using round(x, 2, 1), I get: 25011.790000000001 !
>
> _Why is the round(x, 2, 1) behaving like this anyhow?_
>
> Some samples:
> select pcpc.Credit as c1 -- 25011.799999999999
> , round(pcpc.Credit, 2, 1) as c2 -- 25011.790000000001
> , round(pcpc.Credit, 2) as c3 -- 25011.799999999999
> , cast(cast(pcpc.Credit * 100 as int) as float)/100 as c4 --
> 25011.799999999999
> , convert(nchar(32), round(pcpc.Credit, 2), 0) as c5 --
> 25011.8
> , cast(convert(nchar(32), round(pcpc.Credit, 2), 0) as float) as c6
> -- 25011.799999999999
> from FOO ppc
>
> So a float just can't hold the value 25011.799999999999 exactly, and
> the nearest binary representation is 25011.799999999999 (c6) or
> 25011.790000000001 (c2)? Is that it?
>
> I think I'll stick with column c5 for my download per flatfile to my
> datawarehouse.
>
> Fot those interested: an oracle equivalent:
> select pcpc.Credit as c1 -- 25011.8
> , round(pcpc.Credit, 2) as c3 -- 25011.8
> , to_char(pcpc.Credit, 'FM9999999999.99') -- 25011.8
> from (select 25011.799999999999 as credit from dual) pcpc
>
> Regards,
> Erik Ykema
>

TenKMan

3/30/2007 3:50:00 PM

"ErikYkema" <erik.ykema@gmail.com> wrote in message
news:1175266634.706928.31390@o5g2000hsb.googlegroups.com...
> Hi,
> I am looking at a float field in a SqlServer2000 table.
> In the database I got the following value: 25011.799999999999.
> I like to export this to flatfile using DTS as 25011.80 or 25011.8
> (rounding on two decimals).
>
> When rounding this using round(x, 2, 1), I get: 25011.790000000001 !
>
> _Why is the round(x, 2, 1) behaving like this anyhow?_

Did you read the documentation on the ROUND function in BOL? If so, what is
the return type of the function? Answer - the same type as the first
argument. In this case, it is float. And as you have just discovered, the
float datatype is approximate.

> Some samples:
> select pcpc.Credit as c1 -- 25011.799999999999
> , round(pcpc.Credit, 2, 1) as c2 -- 25011.790000000001
> , round(pcpc.Credit, 2) as c3 -- 25011.799999999999
> , cast(cast(pcpc.Credit * 100 as int) as float)/100 as c4 --
> 25011.799999999999
> , convert(nchar(32), round(pcpc.Credit, 2), 0) as c5 --
> 25011.8
> , cast(convert(nchar(32), round(pcpc.Credit, 2), 0) as float) as c6
> -- 25011.799999999999
> from FOO ppc
>
> So a float just can't hold the value 25011.799999999999 exactly, and
> the nearest binary representation is 25011.799999999999 (c6) or
> 25011.790000000001 (c2)? Is that it?

http://docs.sun.com/source/806-3568/ncg_gol...

> I think I'll stick with column c5 for my download per flatfile to my
> datawarehouse.

So you are implicitly converting from float to nchar(32). Are you aware of
how this conversion is done? If you want only two decimal places, this
conversion does not guarantee that.

declare @test float, @test1 float
set @test = 25011.799
set @test1 = 25.799
select @test, cast (@test as char(20)), @test1, cast (@test1 as char(20))

> Fot those interested: an oracle equivalent:
> select pcpc.Credit as c1 -- 25011.8
> , round(pcpc.Credit, 2) as c3 -- 25011.8
> , to_char(pcpc.Credit, 'FM9999999999.99') -- 25011.8
> from (select 25011.799999999999 as credit from dual) pcpc

Perhaps you really want to cast the float column to numeric(x,2) to
guarantee that only 2 decimal places (with rounding) are exported?

microsoft.public.sqlserver.programming

Rounding: round(x,2) gives 25011.790000000001 for 25011.799999999999

ErikYkema

Jack Vamvas

TenKMan

Tom Cooper

TenKMan

x Login to ForumsZone