Round up to 8 sig fig

[johnnymagee69]

Hi,
I have to re-create in SQL a custom Excel function which rounds numbers to 8 significant figures (because thats how many digits can be shown on a calculator!). I can work out the number of decimal points correctly but the round() function then rounds down when called in a procedure instead of up as it does normally when the digit to truncate is 5. All numbers are type decimal(38,33).

eg
The number to round up is 20.6348035.
When called in a procedure: round(20.6348035,6)=20.6348030 but outside of a procedure: round(20.6348035,6)=20.6348040

Any help appreciated.

Ta

 

[Catadmin]

Try using Round() in conjunction with CEILING(). You'll have to be careful as Ceiling() will try to round the whole number, decimals included, but I'm sure you can work around it.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[johnnymagee69]

Yeah, also tried this but whilst it worked for this example it fails for others (eg fn_round(1.12345678)=1.123456):

create function fn_roundup(@num decimal(38,33),@dp int)
returns decimal(38,33)
as begin
if @dp <0 set @dp=0
return @ceiling(@num*power(10,@dp)) / power(10,@dp)
end