-1

I have this statement (SQL Server 2012):

SELECT ROUND(CAST(50.9685 as float), 3) AS Col1 INTO #Test

I would like to see the result '50.969', but in fact I see '50.968'. When using decimal(10,5) instead of float, for example, then the result will be as expected.

Why does this happen and how can I fix this?

A-Tech
  • 806
  • 6
  • 22
Sergey Alikin
  • 159
  • 10
  • 6
    This is why using a `float` is a bad idea. `50.9685` as a `float` isn't `50.9685`, it's `50.968499999999999000`. As a result, `50.968` *is* the expected value. – Thom A Jun 21 '23 at 13:26
  • @ThomA, thanks for your explanation. What is the solution for this? I tried to use decimal, but it should be hard coded with number of decimals and gives arithmetical overflow when CAST it. – Sergey Alikin Jun 21 '23 at 13:31
  • *"What is the solution for this?"* don't use a `float`, use data type appropriate for decimal values (a `decimal`). – Thom A Jun 21 '23 at 13:32
  • `SELECT ROUND(50.9685,3)` works fine for me. It doesn't given an error. – Thom A Jun 21 '23 at 13:33
  • `gives arithmetical overflow when CAST it.` the maximum is 10^38. What did you try to store? Use a precision and scale that match the data. If something fails, checky why. It's quite possible it's bad data – Panagiotis Kanavos Jun 21 '23 at 13:36
  • `SELECT ROUND(99.99,1)` would generate an error, as an example, due to the value returned being `100.00`, which is too large for a `decimal(4,2)`. Perhaps you have a similar scenario? This question is certainly an [XY Problem](//xyproblem.info). – Thom A Jun 21 '23 at 13:38
  • How did you try to use `decimal`? It works, if you use the correct precision and scale. – Panagiotis Kanavos Jun 21 '23 at 13:44

1 Answers1

1

This is why using a float is a bad idea. 50.9685 as a float isn't 50.9685, it's 50.968499999999999000. As a result, 50.968 is the expected value.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67