Question about the CEILING function (Transact-SQL)

Copper Contributor

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...

 

 

2 Replies

@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

@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