• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 17725
  • Last Modified:

Divison of integers (integer division)

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 ?
0
deusex
Asked:
deusex
2 Solutions
 
jogosCommented:
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 )
0
 
Jai STech ArchCommented:
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...
0
 
Ashish PatelCommented:
Getting straight to your question, the answer is NO, you will have to change your queries by any means.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the answer is   int / int = int
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I'd like to avoid those 3 ways.
agreeing with asvforce and aneeshattingal: you cannot avoid using any of the workarounds. there is no setting in sql server requesting the division of 2 ints to be decimal implicitely.

0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now