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.

---

CEILING(moneyvalue * 100) / 100

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

---

Use Ceiling

select ceiling(1.1349 * 100) / 100

result is 1.14

---

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

---

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