-1

I found a strange behavior when using the ROUND function with the third parameter to truncate a float number:

declare @f2 float = 1.24;
select round(@f2, 2, 1)

Outputs:

1.23

I am fully aware of the approximately nature of floating point types, but it doesn't seem correct for such a "simple" number.

Code run on: Microsoft SQL Server 2019 (RTM-CU18) (KB5017593) - 15.0.4261.1 (X64) Sep 12 2022 15:07:06 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2019 Standard 10.0 (Build 17763: )

werot
  • 9
  • 1
  • Most likely exact value of float 1.24 is something like 1.239999... - and this truncates to 1.23. If you know maximum precision and range of your input values, you can use some decimal (intermediate) variable before truncation. You can see exact value here for example: https://www.exploringbinary.com/floating-point-converter/ – Arvo Nov 18 '22 at 10:45
  • 1
    I suppose the real question is, should you be using a `float`? – Thom A Nov 18 '22 at 11:07
  • Thank you for your answer Arvo, that explains the behavior. A cast do decimal shows it: declare @f2 float = 1.24; select cast(@f2 as decimal(31,30)) – werot Nov 18 '22 at 11:28
  • @Larnu: That's not my decision, it is given. Just thought, that around 1 the float is exact for "simple" numbers and the approximation-delta becomes effective for bigger, smaller, more precise numbers. – werot Nov 18 '22 at 11:37
  • No, there are many non-integer base 10 values that base 2 can't represent accurately @StefanHa. – Thom A Nov 18 '22 at 12:15

2 Answers2

1

In declare @f2 float = 1.24;, the source text 1.24 is converted to the floating-point format IEEE-754 binary64 (also called “double precision”). The result of this conversion is exactly 1.2399999999999999911182158029987476766109466552734375, which is the number representable in the binary64 format that is closest to 1.24. This number equals 5,584,463,537,939,415•2−52. The next greater representable number is 5,584,463,537,939,416•2−52 = 1.2400000000000002131628207280300557613372802734375, which is slightly farther from 1.24.

Then select round(@f2, 2, 1) operates on 1.2399999999999999911182158029987476766109466552734375. Truncating this to two digits after the decimal point produces 1.23.

Eric Postpischil
  • 195,579
  • 13
  • 168
  • 312
0

It does work the way it is supposed to. According to the documentation it does what it is supposed to. Round should round a value to the specified length. Since computers work a little bit different compared to humans, the number 1.24 is technically 1.2399999 for the computer. If you were to use Round the regular way like here

declare @f2 float = 1.24;
select round(@f2, 2)

You would get your variable rounded to the second decimal and you would get the result 1.24. But since you are trying to TRUNCATE, which is done by adding any number different from 0 after the length specified in the Round like you've done in the example you've given us, you are not going to get the number 1.24 truncated, but instead 1.23999999 truncated, which will return 1.23.

Ineffable21
  • 153
  • 1
  • 13
  • 1
    *"Since computers work a little bit different compared to humans, the number 1.24 is technically 1.2399999"* this is a little inaccurate; computers can store 1.24 without issue in the correct data type. The reason it's 1.239999999~ is because the OP is using a `float` which is a base 2 value, which can't represent some non-integer base 10 values accurately. – Thom A Nov 18 '22 at 11:03
  • Yes, float uses base 2 not 10, that was my fallacy :) – werot Nov 18 '22 at 12:10