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,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp