SELECT CEILING(5/2) AS OOPS, CEILING(5.0/2) AS HMMM FROM Schools

In my part of the world
CEILING(5/2)
and
CEILING(5.0/2)
do not evaluate to the same quantity in MS-SQL Server.

As I see it, for CEILING(5/2),noting that 5 and 2 are whole numbers,
SQL decides to truncate the result of the division, returning 2, not
2.5. The ceiling of 2 is 2.

When we introduce 5.0 SQL decides to return a fractional amount, 2.5;
The ceiling of 2.5 is 3.

I search my SQL regularly and write my CEILING (and any other
functions that might be influenced) functions as
CEILING(Cast(a as float) / b).

This situation bites me occasionally until I realize what I have done.

Perhaps you have a comment or a simpler or more global solution.

On Sun, 30 Dec 2007 10:10:55 -0800 (PST), lyle
<lyle.fairfield (AT) gmail (DOT) com> wrote:


From Books Online, on the Divide Operator:
If an integer dividend is divided by an integer divisor, the result is
an integer that has any fractional part of the result truncated.

Thus: SELECT 5/2 = 2

On 30 Dec 2007 20:27:48 GMT, "David W. Fenton"
<XXXusenet (AT) dfenton (DOT) com.invalid> wrote:

When you're using Integer columns you'll have to CAST or CONVERT. In
trivial cases you can write:
select 5.0/2
-> 2.500000

It's just one of those things you learn the hard way.

-Tom.

On Dec 30, 3:27 pm, "David W. Fenton" <XXXuse... (AT) dfenton (DOT) com.invalid>
wrote:

With a literal you can just add a decimal point and a zero as 5.0/2
rather than 5/2.

But when it's a variable Casting the Dividend (or Converting it) as
some non-integer number type is the only way I've found. I'd love to
find something like (SET ANSI_NULLS ON) that would make division
behave like most programming languages with which I'm familiar, but I
haven't.

This has bitten me three times, in 2005, 2006, 2007. And I've learned
it three times. Then I forget about it. Then a year later it hits me
again, and I'm trying to explain why there we're one pizza short.

I don't know how 5/2 works in a Pass Through Query sent through ODBC
to MS-SQL from Access but I'm working directly in MS-SQL so that's
only mildly interesting to me.

On Dec 30 2007, 4:45*pm, lyle <lyle.fairfi... (AT) gmail (DOT) com> wrote:
A shot in the dark:

http://msdn2.microsoft.com/en-us/library/bb508963.aspx

suggests, if I understand it correctly, that the vardecimal storage
format can store (integral) decimal type values almost as efficiently
as it can store integers. A Google search using 'SQL Server
vardecimal' turned up other interesting facts, such as which versions/
editions/SPlevels of SQL Server can make use of it.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com