We help IT Professionals succeed at work.

Divison of integers (integer division)

deusex
deusex asked
on
17,806 Views
Last Modified: 2012-05-05
A quote from http://classicasp.aspfaq.com/general/why-does-4/5-0.html:

"Why does 4 / 5 = 0?
SQL Server
 
You may be startled to see the result of this calculation in SQL Server:
 SELECT 4 / 5
 -----------  
0
 (1 row(s) affected)
 This is because SQL Server is performing integer division... which ignores decimals / fractions / remainders. There are a couple of ways you can force the behavior you really want:
 SELECT 4.0 / 5
 SELECT 4 * 1.0 / 5
 SELECT CAST(4 AS DECIMAL(5,1)) / 5"

There are many JET (MS Access) queries I should translate to T-SQL. I'd like to avoid those 3 ways.
Is there some sql server 2005 setting I should set to force the division to be a floating point division instead of an integer division when both denominator & numerator are integers ?
Comment
Watch Question

Commented:
If one of the factors 4 or 5 is a floating point-type then it automaticaly becomes a floating point-division.
So look at your table-description to see if a cast or convert is needed.

PS: that's the problem with depending on automatic type-conversion It is easy but sometimes you got big problems (like a second server with other regional settings, bug fun with dates etc )
Jai STech Arch

Commented:
here is also a good explanation
http://classicasp.aspfaq.com/general/why-does-4/5-0.html

you probably can write a function and conver the first param using that function...
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
the answer is   int / int = int
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.