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: )

CodePudding user response：

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 closed to 1.24. This number equals 5,584,463,537,939,415•2⁻⁵². The next greater representable number is 5,584,463,537,939,416•2⁻⁵² = 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.

CodePudding user response：

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.