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

sql sql-server rounding

41,030

Solution 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?

Solution 2

Cast whole numbers.

SELECT (cast(50 AS float)/100)

Solution 3

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

You need to use floating point division.

Solution 4

When you are using /(Divide) operator it

Returns the data type of the argument with the higher precedence.

and

If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

So, you need to cast at least one of the operands to appropriate type: decimal and numeric or float or real.

Solution 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.

View more solutions

41,030

JosephStyons

I started out as a professional developer using Delphi and Oracle in a Win32 client-server environment for a manufacturing company. I worked for five years in consulting, implementing solutions for dozens of clients and using many disparate technologies. Since then, I've worked for and with the non-profit industry, building applications that help them move their missions forward. My bread-and-butter is VB.NET and C# against a SQL Server back-end using a SOA architecture. But I can and will use whatever tool gets the job done, and I've had fun doing so with Angular, jQuery, ASP.NET, PHP, and even my own homemade frameworks to deliver solutions against that platform.

Updated on July 09, 2022

Comments

JosephStyons almost 2 years
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?
JosephStyons about 15 years

Dividing by 100.0 works. Simple explanation for a simple problem. Thanks :)
JosephStyons about 15 years

Incidentally, multiplying by 0.01 has the same effect, and is perhaps more obvious.
Rabid over 13 years

The specific 'integer-speak' term is truncation. 75/100 still returns 0. The definition is to simply remove any fractional part from the number.