SQL Server - How to round up or down decimals?

Question

I would like to be abble to round up or down 10.823. Expected result:

rounding down = 10.82
rounding up = 10.83

Knowing that round(10.823, 2) only rounds down. How to round it up?

Answer 1

You are correct, round is the wrong tool for this job. Instead, you should use floor and ceiling. Unfortunately, they do not have a precision parameter like round, so you'd have to simulate it using division and multiplication:

SELECT FLOOR(value * 100) / 100 AS rounded_down,
       CEILING(value * 100) / 100 AS rounded_up
FROM   mytable