sqlroundup

SQL - Rounding up float values to the 2nd decimal place using the away-from-zero midpoint rule


I want to round up a number but CEILINGrounds to first integer. I also tried adding 0.005 but this rounds up the ones under 0.005.

I want to round up if the 3rd decimal is 5 or higher...

please try these numbers before posting your solution

  5.085 --> 5.09
 15.085 --> 15.09
110.4646 --> 110.46
110.4656 --> 110.47

My Code :

DECLARE @i float 
  SET 
    @i = 5.085 
 SELECT 
    ROUND(@i, 1) AS [Result 1], 
    ROUND(@i, 2) AS [Result 2], 
    ROUND(@i, 3) AS [Result 3]

result is

result

Expected result 2 is 5.09 when 3rd decimal is 5 or higher...


Solution

  • if ou convert your float to a higher precision, you can round and it decides alone if with 5 it is get up or with 4 down

        DECLARE @i float ,  @j float
          SET 
            @i = 5.085 
          SET @j = 0.084
         SELECT 
            ROUND(CAST(@i as float(3)), 1,0) AS [Result 1],     
          ROUND(CAST(@i as float(3)), 2,0) AS [Result 2],
          ROUND(CAST(@j as float(3)), 2,0) AS [Result 2],
            ROUND(CAST(@i as float(3)), 3,0) AS [Result 3],
            ROUND(CAST(@j as float(3)), 3,0) AS [Result 3]
    
    Result 1 Result 2 Result 2 Result 3 Result 3
    5.1 5.09 0.08 5.085 0.084

    fiddle