how to round up to decimal place like money

Asked 31/8, 2011 at 19:51 Answered 25/1, 2017 at 17:1

Solved sql sql-server sql-server-2000

I need to round money values up to the nearest cent, then do some operations on that rounded value. I cannot use Round() because this will also round down. These are all money values.

123.4567 --> 123.46
1.1349 --> 1.14

Is there any way to do this in SQL? If I need a UDF, please provide suggestion on how to accomplish code for that UDF.

EDIT: Data is stored as Float.

Fallonfallout answered 31/8, 2011 at 19:51 Comment(2)

What datatype are you using to store these values? – Hackberry 31/8, 2011 at 19:53

Not sure, SQL Server 2000, precision doesn't seem to be specified. Length is 8. – Fallonfallout 31/8, 2011 at 20:4

CEILING(moneyvalue * 100) / 100

perhaps? Convert to pennies, round up to nearest whole penny, then convert back to dollars.

Susuable answered 31/8, 2011 at 19:54 Comment(5)

@Jason: msdn.microsoft.com/en-us/library/ms175003.aspx Round() will round to nearest within the limit specified. OP wants unconditional round-up. – Susuable 31/8, 2011 at 20:9

Funny, the OP is talking about cents and your answer about pennies and dollars. :) Obviously off-topic. Just to have a laugh. – Masakomasan 31/8, 2011 at 20:15

@Marc B, I noticed regarding the value 1.1349. That's why I removed my comment. Just took me a few minutes to get past the word round as this technically isn't a strict rounding. – Kaph 31/8, 2011 at 20:18

@Jason McCreary -I learned it in school as "rounding", "rounding-up", and "rounding-down". There's always many names for techniques. – Fallonfallout 31/8, 2011 at 20:28

@MAW74656, as you'll learn spoken language and technical spec often differ. Most all programming languages round 1.1349 to 1.13 by default. Technically speaking, in your case you're double rounding. That is 3 places, then 2 places after the decimal. Which is effectively what Marc B has provided. – Kaph 31/8, 2011 at 20:52

Use Ceiling

select ceiling(1.1349 * 100) / 100

result is 1.14

Spiculate answered 31/8, 2011 at 19:55 Comment(4)

Have you tried running the select I posted? @Downvoter Please comment! – Spiculate 31/8, 2011 at 19:59

I downvoted because Ceiling by itself is not useful here. Your method works as modified, but I had no way to know thats what you meant. Programming forum, not mind-reading forum. – Fallonfallout 31/8, 2011 at 20:4

How about removing downvote after I updated my answer? Nothing here is written is stone is it? – Spiculate 31/8, 2011 at 20:10

-Yes, your right. Please make an edit to your answer (add a line or something. Stack is saying I can't change my vote until the question is edited. – Fallonfallout 31/8, 2011 at 20:12

Here is a sort of hacky way to get your desired results. Multiply by 100 and take the ceiling - that will round it up the way you want. Then convert it back to money and divide it back down by 100.

declare 
    @Value1 decimal(9,4),
    @Value2 decimal(9,4)

select 
    @Value1 = 123.4567,
    @Value2 = 1.1349

select
    @Value1, @Value2

select 
    convert(money, ceiling(@Value1 * 100)) / 100,
    convert(money, ceiling(@Value2 * 100)) / 100

Soffit answered 31/8, 2011 at 19:58 Comment(1)

Yes, but Marc B beat you to it. – Fallonfallout 31/8, 2011 at 19:59

Here is some code to add precision to it

SET @Precision = CAST('1' + (SELECT REPLICATE('0',@Precision)) as int)
SET @Amount= CEILING(@Amount * @Precision) / @Precision
RETURN @Amount

Often answered 25/1, 2017 at 17:1 Comment(1)

Welcome to StackOverflow. It would be great if you could explain your code so it becomes more useful for others. – Blunge 25/1, 2017 at 17:14

Recommended topics

Hot tags