Author: Steven Neiland
Published:

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.

select
      (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

select
      (
            CAST(1 as float)/2
      ) as result

Result: 0.5

Solution 2: Use CONVERT

select
      (
            CONVERT(FLOAT,1)/2
      ) 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.

select
      (
            (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.

What Do You Think?

Reader Comments

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

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!

Post a Comment

Comment Etiquette:

  • 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 or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub