sqlsql-serverroundingsql-query-store

Sql server round interpreting 5 as lower

I want to round decimals in sql server 2012 for example:

Select ROUND(1.056,2) -- returns  1.06
Select ROUND(1.055,2) -- returns  1.06
Select ROUND(1.054,2) -- returns  1.05

How can I make the second query returns 1.05 rounding the third decimal if 5 to lower?

Solution

  • You can use this. It will work for you.

    DECLARE @test decimal(10,3) = 1.055
SELECT CASE WHEN round(@test,3,1) - round(@test,2,1) = 0.005 THEN round(@test,2,1) ELSE round(@test,2) END