T-SQL ROUND Function Bug?

Let's take a look at an example:

   1: DECLARE @Value float

   2: SET @Value = 12.1785

   3: SELECT '12.1785' as ValueToRound, ROUND(@Value,3) as RoundedValue

   4: SET @Value = 12.1745

   5: SELECT '12.1745' as ValueToRound,ROUND(@Value,3) as RoundedValue 

Guess what the result is? This example comes from: T-SQL ROUND Function Bug

 

1. Cause

In an old system, each month, each business data of the system is summarized to generate receivables. However, each month's accumulation is always different from the continuous accumulation of summary every day (a difference of a few minutes, or a few cents ).

The same problem is that a batch of materials in the warehouse are counted by weight, which is deducted from the warehouse each time. However, the number of materials to be delivered is occasionally the same as the existing quantity in the warehouse, but it still cannot be checked out.

The root cause of the problem lies in the data storage type. In the system, float is used to store data. However, in the computer, approximate values are used to represent float/double. In this +/-process, errors are constantly accumulated, and these problems occur. For the storage format of floating point numbers, refer to my previous article: "exact" to determine whether a floating point number is equal to 0

 

2. Round in Linq to SQL

Knowing the cause of the problem, we can handle it in a targeted manner: round the data and then sum the data.

In C #, two calculation methods are provided to control Rounding: MidpointRounding. toEven and MidpointRounding. awayFromZero; The default value is MidpointRounding. toEven, that is, when a number is the median of the other two numbers, it is rounded to the nearest even number. For details about the rounding method, refer to MSDN: "MidpointRounding enumeration ".

It is worth mentioning that it faces the MidpointRounding. awayFromZero is incorrectly translated. The original Article is: "AwayFromZero: When a number is the center of the other two numbers, it is rounded to a value with a smaller absolute value." However, the original English text is: "When a number is halfway between two others, it is rounded toward the nearest number that is away from zero. "The description of this document is quite poor. I don't know if I have poor understanding skills. Anyway, it's very reliable to look at the examples.

   1: // 3.4 = Math.Round( 3.45, 1)

   2: //-3.4 = Math.Round(-3.45, 1)

   3:  

   4: // 3.4 = Math.Round( 3.45, 1, MidpointRounding.ToEven)

   5: // 3.5 = Math.Round( 3.45, 1, MidpointRounding.AwayFromZero)

   6:  

   7: //-3.4 = Math.Round(-3.45, 1, MidpointRounding.ToEven)

   8: //-3.5 = Math.Round(-3.45, 1, MidpointRounding.AwayFromZero)

 

If Math. round (T. amount, 2, MidpointRounding. awayFromZero), it is resolved to the T-SQL is to directly call the Round function; if you use Math. round (T. amount, 2, MidpointRounding. toEven), it will be parsed:

   1: (CASE 

   2: WHEN ((([t0].[Amount]) * 2) = round(([t0].[Amount]) * 2, 2)) AND (([t0].[Amount]) <> round([t0].[Amount], 2)) THEN round(([t0].[Amount]) / 2, 2) * 2

   3: ELSE round([t0].[Amount], 2)

   4: END)

3. Solution

3.1 twice

One Round can solve the problem of decimal point selection, but it cannot handle the error caused by the floating point precision. The following is the result of one Round:

This is because, for example, the format of floating point 2053.345 stored in a computer may be 2053.2449999999 ......, The result of one Round is directly followed by 49999 ..... . However, we can use two Round rounds to achieve the desired rounding effect: Round (Value, 6), 2)

3.2 Decimal

To solve the precision problem, it is king to define the data type as Decimal.

The storage format of Decimal in calculation: the length of a Decimal value is 128 bits. It consists of a 1-bit symbol, a 96-bit integer, and a proportional factor, the proportional factor is used as the divisor of a 96-digit integer and specifies which part of the integer is a decimal number. The proportional factor is implicitly set to the power of the number 10, and the exponent range is from 0 to 28. Therefore, the binary representation of the Decimal value is (-2 ^ 96 to 2 ^ 96)/10 ^ (0 to 28 )). The proportional factor also retains all trailing zeros in the Decimal number.

Tags: Round, Decimal, and Linq to SQL