Friday, April 2, 2010

TSQL Division

I've lost count of the number of times I've revisited this, so let's document it.

Dividing numbers in sql >

SELECT 73345888 / 394239526
This returns 0. The result has been rounded.

I'll try CASTing the result as a float...
SELECT CAST(73345888 / 394239526 AS FLOAT)
This also returns 0.

If we play with the precision of either the divisor or the dividend, we get what we want...
SELECT 73345888 / 394239526.0
SELECT 73345888.0 / 39423952
Both of these return 0.18604397368

As does casting them before division...
SELECT CAST(73345888 AS FLOAT) / CAST(394239526 AS FLOAT)
or the long way...
DECLARE @numeric1 FLOAT
DECLARE @numeric2 FLOAT
SET @numeric1 = 73345888
SET @numeric2 = 394239526
SELECT @numeric1 / @numeric2

Link : http://www.codeguru.com/forum/showthread.php?t=458369

No comments: