Divide in SQL Server

Question

In SQL Server 2005 Express, the result is below

SELECT 100 / 15 --Result 6

But I wanna get approximate value 7 (like using calculator)

100 / 15 = 6.6666..

How to make it in SQL Server?

Answer 1

You have to use decimal numbers e.g. 100 / 15.0. If you use integers the result is interpreted as an integer and truncated to the largest integer that is smaller than or equal to the result.

Answer 2

You want SQL Server to perform floating-point divison, as opposed to integer division. If you're using literal values, as in your example, suffix the denominator with .0 to force SQL Server to treat it as a floating-point value.

SELECT 100 / 15.0

Otherwise, make sure you declare your variables as FLOAT.

Answer 3

SELECT cast(100 as float) / (15 as float)

Answer 4

Try declaring variables as floats:

DECLARE @var1 FLOAT
DECLARE @var2 FLOAT
SET @var1 = 100
SET @var2 = 15
SELECT @var1 / @var2