Forum Discussion

VHG-IT's avatar
VHG-IT
Copper Contributor
Mar 20, 2024

Question about the CEILING function (Transact-SQL)

Hello,

 

Why do I get a different result for certain tens if I multiply them as INT or FLOAT value by 1.1 and apply CEILING to the operation?

 

DECLARE @A int = 40, @B float = 40;
SELECT CEILING(@A * 1.1), CEILING(@B*1.1);

Results:
44  44

 

DECLARE @A int = 50, @B float = 50;
SELECT CEILING(@A * 1.1), CEILING(@B*1.1);

Results:
55  56

 

DECLARE @A int = 60, @B float = 60;
SELECT CEILING(@A * 1.1), CEILING(@B*1.1);

Results:
66  66

 

I don't understand why with 50 as float the result is 56...

 

 

  • LainRobertson's avatar
    LainRobertson
    Silver Contributor
    Mar 21, 2024

    VHG-IT 

     

    Hi, Tilman.

     

    The short answer is: float is an imprecise storage type.

     

    Here's a longer answer:

     

     

    Here's some formal documentation to back it up:

     

     

    Purely as a curiosity, you'll find you get the "correct" answer for your specific example when using float(24) or less but that doesn't mean you should use it (or real, which is just a float(24)).

     

    If precision is paramount, use decimal instead, but be aware that you ought to specify values for both precision and scale (for example decimal(24, 4)) or you run the risk of running into further issues.

     

     

    Cheers,

    Lain

    • VHG-IT's avatar
      VHG-IT
      Copper Contributor
      Mar 21, 2024

      LainRobertson 

       

      Hi, Lain.

       

      Thanks for your reply. We noticed the problem with a price increase of 10%. The task was to increase all prices and round up to a whole number. I then simply multiplied all prices by 1.1 and applied CEILING. A customer later complained that his purchase price was 1€ too high. After checking, I came across the problem with whole tens. The data type of the price column is FLOAT ;-). For whatever reason...

       

      I will now rebuild the price table and remember not to use FLOAT in SQL.

       

      Best regards
      Tilman

Resources