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