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

Question

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

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

`5.085 - 5.085 =`, when doing decimal minus float, is not equal to 0 , see: https://dbfiddle.uk/IGAtq0Fj (see also: [SQL Server Float data type calculation vs decimal](https://stackoverflow.com/questions/8414561/sql-server-float-data-type-calculation-vs-decimal)) — Luuk, Jun 25 '23 at 12:37

score 0 · Answer 1 · answered Jun 25 '23 at 08:40

0

If you're expecting to work with a decimal, use a decimal data type:

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

answered Jun 25 '23 at 08:40

Stu

30,392
6
14
33

the original data in SQL is float but i can CAST it to decimal... – Umut K Jun 25 '23 at 08:52
This is not clear in your question however yes just cast it as a decimal data type to get the expected rounding behaviour; `float` is almost certainly the wrong data type to be using. – Stu Jun 25 '23 at 08:56
try with number 115.4646. the result should be 115.46 but your solution gives 115.47 – Umut K Jun 25 '23 at 10:25
simply converting solves the problem actually... `convert(Decimal(18,2),@i)` – Umut K Jun 25 '23 at 11:01
You need to specify the approprial scale and precision, the above answer is based soley on your sample data. Using `DECLARE @i Decimal(7,4) = 115.4646` works – Stu Jun 25 '23 at 11:02
when i try `5.085` i get the result `5.08` but when i try `15.085` i get the result as `15.09` what is the difference ? – Umut K Jun 25 '23 at 11:13
please check my revised answer.... – Umut K Jun 25 '23 at 11:18

nbk · Accepted Answer · 2023-06-25T16:25:35.830

0

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

edited Jun 25 '23 at 16:25

answered Jun 25 '23 at 09:03

nbk

45,398
8
30
47

it should decide automatically, if the 3rd decimal is 5 or more , it should round up, if not down... `5.085` --> `5.09` but `115.4646` --> `115.46` – Umut K Jun 25 '23 at 10:35
then i misunderstood ou i changed my answer, to show how you can simply do it in an universal solution – nbk Jun 25 '23 at 14:41
thank you. my last solution was also similar... `Round(CONVERT(DECIMAL(38, 4), @i), 2)` – Umut K Jun 26 '23 at 06:32
Cde inal needs to be adapted here you need only value and which figir you want to reduce – nbk Jun 26 '23 at 07:11

Umut K · Answer 3 · 2023-06-25T11:59:52.907

this is the solution i found with the help of @Stu ... i tried with several sample numbers, so far this works...

DECLARE @i FLOAT = 5.4646
DECLARE @x DECIMAL(38, 4)
DECLARE @y DECIMAL(38, 4)

SET @x = CONVERT(DECIMAL(38, 4), @i)
SET @y = Cast(@i AS DECIMAL(38, 4))

SELECT @x                                    'Converted Number',
       @y                                    'Casted Number',
       Round(@x, 2)                          'Rounded Convert',
       Round(@y, 2)                          'Rounded Cast',
       Round(CONVERT(DECIMAL(38, 4), @i), 2) 'My Solution'

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

3 Answers3