MS-SQL Server and Math

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.

Re: MS-SQL Server and Math

On Sun, 30 Dec 2007 10:10:55 -0800 (PST), lyle
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


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

Re: MS-SQL Server and Math

Tom van Stiphout wrote in
news:rbrfn3pbruu5aavk937qb4d0fmg4r1gcfp@4ax.com:

> 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

So, you have to cast any division that uses whole numbers? Is there
some way to force it without CAST?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: MS-SQL Server and Math

On 30 Dec 2007 20:27:48 GMT, "David W. Fenton"
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.


>Tom van Stiphout wrote in
>news:rbrfn3pbruu5aavk937qb4d0fmg4r1gcfp@4ax.com:
>
>> 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
>
>So, you have to cast any division that uses whole numbers? Is there
>some way to force it without CAST?

Re: MS-SQL Server and Math

On Dec 30, 3:27 pm, "David W. Fenton"
wrote:

> So, you have to cast any division that uses whole numbers? Is there
> some way to force it without CAST?

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.

Re: MS-SQL Server and Math

lyle wrote in
news:679c17ec-9f4c-47ae-8bfa-5be490d366b5@y5g2000hsf.googleg roups.com
:

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

Since Jet doesn't touch it, I'd think it will work exactly the same
as if executed directly in SQL Server.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: MS-SQL Server and Math

On Dec 30 2007, 4:45=A0pm, lyle wrote:
>
> 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.

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@FortuneJames.com