Why does SQL Server round off results of dividing two integers?

Question

I have a table with a smallint column that contains percentages as whole numbers (i.e., 50, 75, 85, etc.)

When I divide this column by 100, as in

SELECT MY_COLUMN/100 AS PCT_AS_FRACTION
FROM MY_TABLE

the result is rounded to the nearest whole number. For example, for a row that contains the number "50", I get zero as my result.

I can duplicate this with a simple statement:

SELECT 50 / 100 AS TEST_VALUE

Why is that, and how can I get more precision in my result?

Answer 1

When you do integer division (integer divided by integer) you always get an integer answer. 50/100 = .50, which is 0 in integer-speak.

Have you tried dividing MY_COLUMN by 100.0?

Answer 2

You're doing integer division. 50/100 is 0 with a remainder of 50.

You need to use floating point division.

Answer 3

Cast whole numbers.

SELECT (cast(50 AS float)/100)

Answer 4

You're dividing 2 integers which results in another integer.

It should possible to cast that way, too

SELECT (50/100)::numeric;

Answer 5

NB - Be careful in that the remainder of integer division is not rounded. Rather, it is dropped. This is equivalent to calling the FLOOR sql function.

This is common, and is defined as such because when multiplying two integers, a fraction will never occur. Therefore a fraction-handling methodology is never assumed when multiplying integers, but the same cannot be said for integer division.

This can often have an impact when doing dateTime arithmetic in SQL.

Hope it helps, Milan