Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Rounding up to fifth decimal Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, April 8, 2014 11:17 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, October 29, 2014 7:28 AM Points: 30, Visits: 86
 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.00382000I'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-Enthusiastic Group: General Forum Members Last Login: 2 days ago @ 8:15 AM Points: 117, Visits: 812
 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 workEdit: corrected boneheaded arithmetic error. -Doughttp://xkcd.com/327/
Post #1559605
 Posted Tuesday, April 8, 2014 11:59 AM This worked for the OP
 Hall of Fame Group: General Forum Members Last Login: Today @ 3:36 PM Points: 3,943, Visits: 8,947
 Maybe you didn't play enough with the ceiling function. `DECLARE @num numeric(10, 8) = 83.00381433 ;SELECT CEILING( @num * 100000) / 100000;` Luis C.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?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 Group: General Forum Members Last Login: Wednesday, October 29, 2014 7:28 AM Points: 30, Visits: 86
 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

 Permissions