Interesting Use Case Of CEILING

Question

Interesting Use Case Of CEILING

logitestus

SSCrazy

Points: 2928
More actions
December 12, 2018 at 2:46 pm

#369367

I have ran into this while writing a reporting stored procedure. I have found that CEILING will increase to the next higher integer even if the value is even.
Here is the example:
DECLARE @percentComplete float = 0.55 select CEILING(@percentComplete * 100)
The return value will be 56. (Which is incorrect.)
But if you do this:
DECLARE @percentCompleteDec DECIMAL(18,2) = 0.55 select CEILING(@percentCompleteDec * 100)
The return value will be 55. (Which is correct.)
I cannot explain why. I have been google-ing/bing-ing around on the internet but cannot find an explanation. Maybe someone here can help me out with this one.
Thanks!

Viewing 9 posts - 1 through 8 (of 8 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Float values are not exact, they are approximate. Internally, float 0.55 might be 0.550000000001. CEILING goes up to the next int value no matter how small the amount above the previous int value is. That's my best guess as to an explanation.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

I think this might answer your question:
DECLARE @percentComplete float = 0.55 select CONVERT(decimal(38,38),@percentComplete )

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

Jonathan AC Roberts - Wednesday, December 12, 2018 4:16 PM
I think this might answer your question:
DECLARE @percentComplete float = 0.55 select CONVERT(decimal(38,38),@percentComplete )

On my system, it explains nothing because I get 0.55000000000000000000000000000000000000 from that code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Jeff Moden - Wednesday, December 12, 2018 8:52 PM
Jonathan AC Roberts - Wednesday, December 12, 2018 4:16 PM
I think this might answer your question:
DECLARE @percentComplete float = 0.55 select CONVERT(decimal(38,38),@percentComplete )
On my system, it explains nothing because I get 0.55000000000000000000000000000000000000 from that code.

On mine I get:0.55000000000000004440892098500626161695

David Burrows SSC Guru Points: 65142 More actions · Answer 5

Jonathan AC Roberts - Thursday, December 13, 2018 2:48 AM
Jeff Moden - Wednesday, December 12, 2018 8:52 PM
Jonathan AC Roberts - Wednesday, December 12, 2018 4:16 PM
I think this might answer your question:
DECLARE @percentComplete float = 0.55 select CONVERT(decimal(38,38),@percentComplete )
On my system, it explains nothing because I get 0.55000000000000000000000000000000000000 from that code.
On mine I get:0.55000000000000004440892098500626161695

SQL 2008R2 returns
0.55000000000000000000000000000000000000

SQL 2016 returns
0.55000000000000004440892098500626161695

Far away is close at hand in the images of elsewhere.
Anon.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

David Burrows - Thursday, December 13, 2018 7:22 AM
Jonathan AC Roberts - Thursday, December 13, 2018 2:48 AM
Jeff Moden - Wednesday, December 12, 2018 8:52 PM
Jonathan AC Roberts - Wednesday, December 12, 2018 4:16 PM
I think this might answer your question:
DECLARE @percentComplete float = 0.55 select CONVERT(decimal(38,38),@percentComplete )
On my system, it explains nothing because I get 0.55000000000000000000000000000000000000 from that code.
On mine I get:0.55000000000000004440892098500626161695
SQL 2008R2 returns
0.55000000000000000000000000000000000000
SQL 2016 returns
0.55000000000000004440892098500626161695

This should return a value other than 0.55 on both versions of SQL Server:
DECLARE @percentComplete float(24) = 0.55 select CONVERT(decimal(38,38),@percentComplete )
I get 0.55000001192092896000000000000000000000 on SQL 2012.

Michael L John One Orange Chip Points: 27055 More actions · Answer 7

Jonathan AC Roberts - Thursday, December 13, 2018 9:45 AM
David Burrows - Thursday, December 13, 2018 7:22 AM
Jonathan AC Roberts - Thursday, December 13, 2018 2:48 AM
Jeff Moden - Wednesday, December 12, 2018 8:52 PM
Jonathan AC Roberts - Wednesday, December 12, 2018 4:16 PM
I think this might answer your question:
DECLARE @percentComplete float = 0.55 select CONVERT(decimal(38,38),@percentComplete )
On my system, it explains nothing because I get 0.55000000000000000000000000000000000000 from that code.
On mine I get:0.55000000000000004440892098500626161695
SQL 2008R2 returns
0.55000000000000000000000000000000000000
SQL 2016 returns
0.55000000000000004440892098500626161695
This should return a value other than 0.55 on both versions of SQL Server:
DECLARE @percentComplete float(24) = 0.55 select CONVERT(decimal(38,38),@percentComplete )
I get 0.55000001192092896000000000000000000000 on SQL 2012.

Here's what I get on various versions.

SQL 2017
0.55000001192092895507812500000000000000
SQL 2016
0.55000001192092895507812500000000000000
SQL 2014
0.55000001192092896000000000000000000000
SQL 2012
0.55000001192092896000000000000000000000
SQL 2008 R2
0.55000001192092896000000000000000000000
SQL 2005
0.55000001192092896000000000000000000000

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

logitestus SSCrazy Points: 2928 More actions · Answer 8

ScottPletcher - Wednesday, December 12, 2018 3:02 PM
Float values are not exact, they are approximate. Internally, float 0.55 might be 0.550000000001. CEILING goes up to the next int value no matter how small the amount above the previous int value is. That's my best guess as to an explanation.

Given the fact that it seems that the float data-type is not exact (as proven by so many of your responses), I am going to vote this as the answer.

Thank you everyone for your help in answering this question. Even though I had an answer, I was really looking for the "why" of it.