Rounding down decimal numbers in SQL Server 2008

Question

I read all rounding functions of T-SQL like Round, Floor, and Ceil, but none of them has rounded down decimal numbers correctly for me.

I have 2 questions:

1. How to round down a decimal number (3.69 ==> 3.5)?
2. How to round up the last 3 digits of an integer (e.g. 142600 ==> 143000)?

Answer 1

1) 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.69 => 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.

Online references with examples are available for T-SQL FLOOR, CAST, and decimal to help.

2) select ROUND(142600, -3) handles the second case.

A similar online reference is available for T-SQL ROUND.