We help IT Professionals succeed at work.

# Divison of integers (integer division)

on
17,806 Views
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

## View Solutions Only

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 )
Tech 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...
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
Database Consultant
CERTIFIED EXPERT
Top Expert 2009

Commented:
the answer is   int / int = int
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 the solution to this question.