I read all rounding functions of T-SQL like
Ceil, but none of them has rounded down decimal numbers correctly for me.
I have 2 questions:
- How to round down a decimal number (
- How to round up the last 3 digits of an integer (e.g.
select CAST(FLOOR(2 * 3.69) / 2 AS decimal(2, 1)) handles the first case - courtesy of an answer to a similar question on SQL Server Forums, which I adapted and quickly checked.
Note that if the numbers you are rounding to the nearest
0.5 could be bigger (e.g.
333.5), be sure to specify more
decimal precision when you cast (e.g.
select CAST(FLOOR(2 * 3.69) / 2 AS decimal(10, 1))), or you could get an overflow error:
Msg 8115, Level 16, State 8, Line 1 Arithmetic overflow error converting numeric to data type numeric.
Extra precision will not affect the bottom-line result (i.e.
select CAST(FLOOR(2 * 3.69) / 2 AS decimal(10, 1)) and
select CAST(FLOOR(2 * 3.69) / 2 AS decimal(2, 1)) both yield
3.5); but it is wasteful if the numbers you are rounding will always be smaller.
select ROUND(142600, -3) handles the second case.
A similar online reference is available for T-SQL