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:

- How to round down a decimal number (
`3.69`

==>`3.5`

)? - How to round up the last 3 digits of an integer (e.g.
`142600`

==>`143000`

)?

**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`

.