Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rounding up to fifth decimal Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2014 11:17 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:06 AM
Points: 28, Visits: 78
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.
Post #1559598
Posted Tuesday, April 8, 2014 11:30 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:35 PM
Points: 93, Visits: 586
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/
Post #1559605
Posted Tuesday, April 8, 2014 11:59 AM This worked for the OP Answer marked as solution


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:25 PM
Points: 3,333, Visits: 7,193
Maybe you didn't play enough with the ceiling function.
DECLARE @num numeric(10, 8) =  83.00381433 ;
SELECT CEILING( @num * 100000) / 100000;




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1559629
Posted Tuesday, April 8, 2014 1:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:06 AM
Points: 28, Visits: 78
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.
Post #1559674
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse