0

Round functions has two behaviors: With the value cours is equal to "3.1235", round(cours, 3) = 3.123. Although, when we replace cours by its value (3.1235) in this round formula, round(3.1235, 3) = 3.1240.

Sami Kobbi
  • 309
  • 1
  • 7
  • 17
  • What type is `cours`? – Mureinik Oct 12 '16 at 14:31
  • the type of cours is float. – Sami Kobbi Oct 12 '16 at 14:32
  • Can you reproduce your problem? There is something you aren't telling us since these are equal...`declare @cours decimal (5,4) = 3.1235 select round(@cours, 3) select round(3.1235, 3) ` – S3S Oct 12 '16 at 14:34
  • I am using float type not decimal, this won't work with a float number. – Sami Kobbi Oct 12 '16 at 14:39
  • 2
    Possible duplicate of [Is floating point math broken?](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) – Jamiec Oct 12 '16 at 14:43
  • You've made a typo or a thinko, because there is no way that `3.something` *ever* rounds to `5.something` without additional manipulation. It *is* true that a value which displays somewhere as `3.1235` might not round to `3.1240` (because it's *really* `3.1234999999999`, for example), but that's a representation issue where the value is already being rounded under the covers. – Jeroen Mostert Oct 12 '16 at 14:51
  • Yes @JeroenMostert, I have made a typo and I have corrected it – Sami Kobbi Oct 12 '16 at 14:55
  • In that case, this is definitely a duplicate of all the other "why do I see weird rounding" questions. What's displayed on your screen as "3.1235" is most probably a rounded representation of a value that is too far away from "3.1235" to round to "3.124". In other words, in the first case, `cours` is *not* equal to 3.1235, but merely close enough to display as such. – Jeroen Mostert Oct 12 '16 at 15:05

2 Answers2

0

You shouldn't be using the float datatype for specific decimal values like this as it's not designed for that purpose. Would need to see more of your code to get a better context of what you're trying to do, but if it needs to be a float initially, potentially you could cast @cours as decimal?

round(cast(@cours as decimal(5,4)), 3)

finjo
  • 366
  • 4
  • 19
0

Your FLOAT does not really contain 3.1235, that is only what is printed or shown in a grid. Internally the FLOAT is 3.1234999999999999, which is obviously rounded down to 3.123.

The literal 3.1235 becomes a NUMERIC with enough precision to be totally exact, and so it is rounded up to 3.124, as one would expect.

Proof:

SELECT CAST('3.1235' as FLOAT),
       CAST( 3.1235  as FLOAT)
-- misleading output: both print 3.1235

SELECT CAST(CAST('3.1235' as FLOAT) as NUMERIC(24,23)),
       CAST(CAST( 3.1235  as FLOAT) as NUMERIC(24,23))
-- both print 3.12349999999999990000000

SELECT CAST('3.1235' as NUMERIC(24,23)),
       CAST( 3.1235  as NUMERIC(24,23))
-- both print 3.12350000000000000000000
Peter B
  • 22,460
  • 5
  • 32
  • 69