how truncate scientific notation from Real?

[mo]

Hi
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.
Help!
Thanks Moe

[Erland Sommarskog]

Re: how truncate scientific notation from Real?

mo (mo@idcomm.com) writes:[color=blue]
> 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.[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[mo]

Re: how truncate scientific notation from Real?

Thank you so much! The convert with decimal worked great!
Moe