Round function doesn't seem to work on floats

Round function doesn't seem to work on floats

Post by Leopol » Thu, 05 Jul 2001 20:03:04



SQL Server 2000, no service pack.

SELECT ROUND (CAST( 24.399999999999999 as float), 2)
-----------------------------------------------------
24.399999999999999

SELECT ROUND (CAST( 24.399999999999999 as real), 2)
-----------------------------------------------------
24.399999999999999

SELECT ROUND (CAST( 24.399999999999999 as money), 2)
---------------------
24.4000

SELECT ROUND (CAST( 24.399999999999999 as decimal(8,4)), 2)
----------
24.4000

Do you observe the same? Am I missing something?

 
 
 

Round function doesn't seem to work on floats

Post by Tony Rogerso » Thu, 05 Jul 2001 20:22:40


real and float data types are known as 'approximate' ones, therefore, the
inaccuracy is not unsuprising.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK SQL Server User Group - FAQ, SQL Tutorials etc...]


Quote:> SQL Server 2000, no service pack.

> SELECT ROUND (CAST( 24.399999999999999 as float), 2)
> -----------------------------------------------------
> 24.399999999999999

> SELECT ROUND (CAST( 24.399999999999999 as real), 2)
> -----------------------------------------------------
> 24.399999999999999

> SELECT ROUND (CAST( 24.399999999999999 as money), 2)
> ---------------------
> 24.4000

> SELECT ROUND (CAST( 24.399999999999999 as decimal(8,4)), 2)
> ----------
> 24.4000

> Do you observe the same? Am I missing something?


 
 
 

Round function doesn't seem to work on floats

Post by Biz » Fri, 06 Jul 2001 01:18:23


<<inaccuracy is not unsuprising.>>

So many negatives...


> real and float data types are known as 'approximate' ones, therefore, the
> inaccuracy is not unsuprising.

> --
> Tony Rogerson SQL Server MVP
> Torver Computer Consultants Ltd
> www.sql-server.co.uk [UK SQL Server User Group - FAQ, SQL Tutorials
etc...]



> > SQL Server 2000, no service pack.

> > SELECT ROUND (CAST( 24.399999999999999 as float), 2)
> > -----------------------------------------------------
> > 24.399999999999999

> > SELECT ROUND (CAST( 24.399999999999999 as real), 2)
> > -----------------------------------------------------
> > 24.399999999999999

> > SELECT ROUND (CAST( 24.399999999999999 as money), 2)
> > ---------------------
> > 24.4000

> > SELECT ROUND (CAST( 24.399999999999999 as decimal(8,4)), 2)
> > ----------
> > 24.4000

> > Do you observe the same? Am I missing something?

 
 
 

Round function doesn't seem to work on floats

Post by Tony Rogerso » Thu, 05 Jul 2001 22:25:13


:)

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK SQL Server User Group - FAQ, SQL Tutorials etc...]


> <<inaccuracy is not unsuprising.>>

> So many negatives...



> > real and float data types are known as 'approximate' ones, therefore,
the
> > inaccuracy is not unsuprising.

> > --
> > Tony Rogerson SQL Server MVP
> > Torver Computer Consultants Ltd
> > www.sql-server.co.uk [UK SQL Server User Group - FAQ, SQL Tutorials
> etc...]



> > > SQL Server 2000, no service pack.

> > > SELECT ROUND (CAST( 24.399999999999999 as float), 2)
> > > -----------------------------------------------------
> > > 24.399999999999999

> > > SELECT ROUND (CAST( 24.399999999999999 as real), 2)
> > > -----------------------------------------------------
> > > 24.399999999999999

> > > SELECT ROUND (CAST( 24.399999999999999 as money), 2)
> > > ---------------------
> > > 24.4000

> > > SELECT ROUND (CAST( 24.399999999999999 as decimal(8,4)), 2)
> > > ----------
> > > 24.4000

> > > Do you observe the same? Am I missing something?

 
 
 

Round function doesn't seem to work on floats

Post by Dave Waterwort » Fri, 06 Jul 2001 17:02:41


This is a fairly common "problem". Rounding 2.39999999. to 2 dp. should
gives 2.4 when using infinite precission math, however 2.4 cannot be
represented exactly in BASE2 (binary), the closest possible approximation is
2.3999999999

This is because 1/10th is a repeating fraction when represented in BASE2
(just like 1/3 is a repeating fraction when represented as BASE10).


Quote:> SQL Server 2000, no service pack.

> SELECT ROUND (CAST( 24.399999999999999 as float), 2)
> -----------------------------------------------------
> 24.399999999999999

> SELECT ROUND (CAST( 24.399999999999999 as real), 2)
> -----------------------------------------------------
> 24.399999999999999

> SELECT ROUND (CAST( 24.399999999999999 as money), 2)
> ---------------------
> 24.4000

> SELECT ROUND (CAST( 24.399999999999999 as decimal(8,4)), 2)
> ----------
> 24.4000

> Do you observe the same? Am I missing something?