0

Why does rounding in SQL Server behave non-intuitively?

SELECT CAST(     19.845           AS DECIMAL(18, 2)) AS [DecOK]
      ,CAST(CAST(19.845 AS FLOAT) AS DECIMAL(18, 2)) AS [DecBad]
      ,CAST(19.845 AS FLOAT)          AS [Float]
      ,CAST(19.845 AS FLOAT) - 19.845 AS [NoDiff]
      ,ROUND(     19.845          , 2) AS [RoundOK]
      ,ROUND(CAST(19.845 AS FLOAT), 2) AS [RoundBad]

Actual:

DecOK   DecBad  Float   NoDiff  RoundOK RoundBad
19.85   19.84   19.845  0       19.850  19.84

Expected:

DecOK   DecBad  Float   NoDiff  RoundOK RoundBad
19.85   19.85   19.845  0       19.850  19.85

I would expect the value to be the same, regardless of the data type. How can I guarantee accurate results when working with FLOATs? Is the NoDiff value implicitly converting the second value to a FLOAT which is why the result is exactly 0?

CJ Dennis
  • 4,226
  • 2
  • 40
  • 69
  • 1
    https://stackoverflow.com/questions/9526386/rounding-issue-with-sql-server-and-real-datatype – artm Jul 07 '17 at 03:35
  • *How can I guarantee accurate results when working with FLOATs* Answer is **Big No**. In MSDN it is clearly stated that "*Float is Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly*" – Pரதீப் Jul 07 '17 at 04:36

1 Answers1

0

Float is an approximate precision. If you want precision, use DECIMAL or NUMERIC which are the same thing.

There are a lot of posts on this but here is a great one.

https://stackoverflow.com/a/7158770

S3S
  • 24,809
  • 5
  • 26
  • 45
  • Thanks for that. I'm working with an existing table created over a year ago. I'm multiplying two numbers, one with 2 decimal places, one with 3 decimal places, but stored as a FLOAT. Now I'm converting the result to DECIMAL(18, 5) before converting it to DECIMAL(18, 2) and the rounding is correct. – CJ Dennis Jul 07 '17 at 04:00