Post reply

Trouble with ceiling() function

  • October 25, 2010 at 3:12 pm

    #230794

    I'm puzzled by the behavior we're getting from ceiling().

    Select ceiling(2.5) results in 3

    Select ceiling(5/2) results in 2

    (SQL Server 2008)

    Am I missing something? We would like to always round up in this case. Thanks for any help.

  • October 25, 2010 at 3:21 pm

    #1241963

    It has to do with significant digits and rounding.

    If you do select 5/2, you get 2 because it truncates.

    If you do select 5.0/2.0, you get 2.500000, which is more accurate.

    edit: Significant Figures rather..but both terms are interchangeable for the most part. Article here, if you're curious: http://en.wikipedia.org/wiki/Significant_digits

  • October 25, 2010 at 3:27 pm

    #1241968

    5/2 is integer math, so it produces integer results, and has nothing to do with how the ceiling function works. See the example below for what is happening.

    select x = 5/2, y = 5.0/2.0

    Results:

    x y

    ----------- ----------

    2 2.500000

  • October 26, 2010 at 7:59 am

    #1242278

    Thanks... that makes sense. I'm surprised I haven't run into that before, but then we don't have many integer data types here. I suppose I can cast the parameters to another type and get the desired result.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply