Posted by Erland Sommarskog on 07/02/05 01:07

mo (mo@idcomm.com) writes:
> We've got some numbers stored as Reals which are returning values in
> scientific notation that we need rounded down to 3 digits to the right
> of the decimal.
> ie 8.7499999E-2 needs to return 8.75.
> Round, cast, convert, formatnumber in the dts package all fail.

It's seems a little funny that 0.0875 would be rounded to 8.75. Maybe
you could clarify?

Anyway, a real is an approximate number. Most numbers cannot represented
exactly as real. How a real number is displayed, depends on the routine
that converts it to text. For instance when I run

select convert(real, 8.7499999E-2)

in Query Analyzer, I see just that. But if I run the same statement in
SQL Server Mgmt Studio, the replacement for Query Analyzer (and
Enterprise Manager) in SQL 2005, I get back 0,875.

If you want make sure that the client only presents three digits, you
could to this:

select convert(decimal(10, 2), 8.7499999E-2 * 100)

but it's probably better to do this in the client.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]