Divison of integers (integer division)

Posted on 2007-10-12
Last Modified: 2012-05-05
A quote from

"Why does 4 / 5 = 0?
SQL Server
You may be startled to see the result of this calculation in SQL Server:
 SELECT 4 / 5
 (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

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 ?
Question by:deusex
    LVL 25

    Expert Comment

    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 )
    LVL 14

    Expert Comment

    here is also a good explanation

    you probably can write a function and conver the first param using that function...
    LVL 23

    Accepted Solution

    Getting straight to your question, the answer is NO, you will have to change your queries by any means.
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    the answer is   int / int = int
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    >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.


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now