CODEWITHSUNDEEP
sqlsql-serversql-server-2000
MAW74656
MAW74656

Reputation: 3539

how to round up to decimal place like money

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.

Upvotes: 9

Views: 22094

Answers (4)

jt007superman
jt007superman

Reputation: 11

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

Upvotes: 1

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

Use Ceiling

select ceiling(1.1349 * 100) / 100

result is 1.14

Upvotes: 5

Derek
Derek

Reputation: 23228

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

Upvotes: 1

Marc B
Marc B

Reputation: 360602

CEILING(moneyvalue * 100) / 100

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

Upvotes: 15

Related Questions