1

I've found a really weird behavior in SQL Server 2012, the CEILING of 100 gives me 101 and sometimes 100.

I need to get the ceiling of a number considering 2 decimals, that means convert a 0.254 to 0.26

So I tried to run 

SELECT CEILING(field * 100.0) / 100.0 
FROM Table

This should work, and it does, at least for most of the data.

Any idea on how to solve it?

This is an example of the weird behavior

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gabriel Espinoza
  • 385
  • 1
  • 18

1 Answers1

1

What you are seeing here is floating point errors. When you store a number in a floating point column, it isn't exact, so the number 1 may actually be 1.0000000000000000000000001. So multiplying it by 100 gives you a number a tiny bit greater than 100, hence CEILING rounds it up to 101.

The solution is to ROUND the number first which will remove the floating point errors. Note I have used 5 as the number of decimal places, you will need to decide on your own value of precision.

SELECT CEILING(ROUND(field,5)*100.0)/100.0 FROM Table
DavidG
  • 113,891
  • 12
  • 217
  • 223
  • ¿should I round it to 10 decimals before applying the CEILING? this seems like a workaround. how should I suppose to handle this correctly? – Gabriel Espinoza Oct 15 '14 at 00:26
  • Maybe not 10, how many do you really need in your application? Anything more than 2 decimal places is rarely required, but only you know your problem domain. – DavidG Oct 15 '14 at 00:28