SQL Server - Round a number when it's one decimal before .50

Question

I would like to know if there is a way to round a number if it's one decimal before 50.

i.e,

I am using the below code and it works, it gives 8.50:

select Round(8.49,1) = 8.50

The problem comes when I want to round the below one, I would expect this one to be only 8.48 but results in 8.50 as well.

select Round(8.48,1) = 8.50

The idea is to keep the 8.48 as it is, but when it's 8.49, it needs to be 8.50.

Answer 1

If you want something other than a standard rounding, you will probably need to use a nested CASE statement.

Maybe take something like the below and tweak it:

DECLARE @Num DEC (10,2) = 8.49 --8.48 

SELECT 
CASE 
WHEN @Num - FLOOR(@Num) >= 0.5 THEN ROUND(@Num,1)
WHEN @Num - FLOOR(@Num) = 0.49 THEN FLOOR(@Num) + 0.5 
ELSE @Num
END AS NewValue