Division in SQL Server gives me an integer not a decimal

I was surprised to learn that SQL Server thinks that 3/2 is 1 instead of 1.5.

Try it for yourself: select 3/2

Result: 1

 

However, I also learned that it thinks 3.0 / 2 and 3/2.0 is 1.5 as we would hope.

Try it for yourself: select 3.0/2

Result: 1.5

 

Try it for yourself: select 3/2.0

Result: 1.5

 

It doesn’t help to cast the result to a float either. I assume this is because the division is done first and changed to an integer and then we are asking that integer to change to a float. In this case, the decimals are gone and cannot be brought back. The same goes for trying convert instead of cast because they are basically the same in this case.

Try it for yourself: select cast(3/2 as float)

Result: 1

 

Conclusion

What this tells me is that if we have integer / integer we will get an integer, not an float, decimal, etc. This also tells me that if one of the numbers in the division is a float, decimal, etc, then the result will be of that type.

Try it for yourself: select cast(3 as float)/2

Result: 1.5

 

Try it for yourself: select 3/cast(2  as float)

Result: 1.5

 

Try it for yourself: select cast(3 as float)/cast(2 as float)

Result: 1.5

 

NOTE: These examples use hard coded numbers, but the same results apply if you had columns in their places.