-2

Today, I found a bug with CEILING(). I don't know why.

Who can give me an answer, pls.

Thanks.

When I test CEILING() SQL Server 2012:

enter image description here

jarlh
  • 42,561
  • 8
  • 45
  • 63
Duc Anh Trinh
  • 11
  • 1
  • 7
    you are using FLOAT. Float is approximate value. Change to decimal and try – Squirrel Mar 29 '16 at 10:09
  • On top it seemswhile you can fid a bug, you never actually bothered learning how to make a bug report. What are we looking at? Where you THINK is the error? WHat you think should be the correct value? – TomTom Mar 29 '16 at 10:09
  • Possible duplicate of [Is floating point math broken?](http://stackoverflow.com/questions/588004/is-floating-point-math-broken) – AakashM Mar 29 '16 at 10:18
  • Just for reference, https://connect.microsoft.com/SQLServer – Liesel Mar 29 '16 at 10:19

1 Answers1

4

This is not a bug per se. A floating point cannot represent 0.17 exactly, since it cannot be represented as a finite sum of (negative) powers of 2. Therefore 0.17 is represented by the closest representable number.

As a result 0.17 is actually represented as something like 0.170000...1. Therefore performing CEILING will represent it as 1701.

As @Squirrel says, you can use a DECIMAL which stores a number exactly for a given amount of digits.

Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555