Prevent SQL Server Rounding To An Integer When Dividing

Author: Steven Neiland

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

I was recently working on some old code that performed some basic calculations in a sql query when I noticed that a number I expected to be a decimal value was sometimes coming out as a whole number even though the correct value should have a decimal component.

The problem was that both the numerator and the denominator were whole numbers. This is where SQL Server shows how dumb it can be sometimes. Since both numbers where integers (even though the column the numerator value came from was defined as a float) it tried to convert the result to an integer as well.

This problem shows up when you are performing mathematical operations such as division or averaging on all whole numbers. To illustrate run this simple query.

      (1/2) as result

Result: 0

You would expect the result to be "0.5" but what we actually get is "0".

The solution to this is to CAST or CONVERT the numerator to a float in the query so that SQL Server does not round the result to a whole number.

Solution 1: Use CAST

            CAST(1 as float)/2
      ) as result

Result: 0.5

Solution 2: Use CONVERT

      ) as result

Result: 0.5

Solution 3: Multiply By 1.0

I came across this solution a few years ago on a forum. By multiplying the numerator by 1.0 we again get a float. However this time instead of getting "0.5" we get "0.500000". I believe that this is because sql server expects the result to be of type 'decimal' and not 'float'. In most cases this doesn't matter anyway but its good to be aware of the difference.

            (1 * 1.0)/2
      ) as result

Result: 0.500000

A Note On MySQL

I have not seen the behavior on my development copy of MySQL (5.5.11), but I cannot say definitively that it does not exist of older versions...though I would be surprised if it did.

Reader Comments

Ada Pineda's Gravatar
Ada Pineda
Thursday, June 5, 2014 at 12:26:39 PM Coordinated Universal Time

Thank you so much! I wasn't able to see that the problem could be solved as simple as this! You saved a lot of time to me!

ANAS ANSARI's Gravatar
Wednesday, August 15, 2018 at 11:06:08 AM Coordinated Universal Time

amaizing... it helps alot, Thank u

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing