T-SQL round special cases

Question

I need a way to round in T-SQL (SQL Server 2005).

0.0041 -> 0.005
0.0049 -> 0.005
0.0040 -> 0.004

I have tried the following select with ROUND():

SELECT ROUND(0.004, 3,1) + .001

The problem is: it doesn't work with 0.0040 -> 0.004

Answer 1

Try this:

SELECT X 
, CEILING(X * 1000) / 1000 AS X4
FROM (
SELECT 0.0041 X UNION ALL 
SELECT 0.0049 UNION ALL 
SELECT 0.0040 ) A

Output:

X                                       X4
--------------------------------------- ---------------------------------------
0.0041                                  0.005000
0.0049                                  0.005000
0.0040                                  0.004000

Answer 2

This:

SELECT CEILING(1000 * 0.0041) / 1000.0

produces:

0.005

whereas this:

SELECT CEILING(1000 * 0.004) / 1000.0

produces:

0.004