Rounding up to fifth decimal

Question

Post reply

Rounding up to fifth decimal

tarr94

SSCommitted

Points: 1684
More actions
April 8, 2014 at 11:17 am

#287332

Hi,
I have a field that stores 8 decimals, but I want to select this field in a query with the decimals rounded up to the fifth. The rounding up should always occur, even if the number is below five. For example:
83.00381433 rounds up to 83.00382000
I've played around with the CEILING function, but I'm not sure how to make it apply to the fifth decimal place.
Any help would be greatly appreciated!
Thank you.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Doug J. SSC Eights! Points: 832 More actions · Answer 1

Interesting question. Try putting together a CASE statement using the ROUND() function. Something like this:

declare @num numeric(10, 8) = 83.00381433 ;

SELECT CASE

WHEN round(@num, 5) - @Num < 0 THEN round(@num, 5) + .00001

ELSE round(@num, 5) END;

I'm not sure if this is the best way to do it, but it should work

Edit: corrected boneheaded arithmetic error.

-Doug

http://xkcd.com/327/

Luis Cazares SSC Guru Points: 183698 More actions · Answer 2

Maybe you didn't play enough with the ceiling function. 😉

DECLARE @num numeric(10, 8) = 83.00381433 ;

SELECT CEILING( @num * 100000) / 100000;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tarr94 SSCommitted Points: 1684 More actions · Answer 3

Thank you both for responding. I tried the solution from Luis first (it was the simpler of the two) and found that it works like a charm. Guess I gave up on the Ceiling function too quickly.