Dividing in SQL

Question

can anyone explain to me why this query returns 0.000?

SELECT CAST(20/1024 AS NUMERIC(10,3))

I am just trying to do a simple conversion of MB to GB

Thanks

Answer 1

You are doing an integer division first and then convert the result. The integer division results in 0. Converting 0 to NUMERIC(10,3) results in 0.000.

SQL Fiddle

MS SQL Server 2008 Schema Setup:

Query 1:

SELECT 20/1024;

Results:

| COLUMN_0 |
------------
|        0 |

To get a non-integer-division you need to convert at least on of the two numbers involved to something other than an integer. In the case of number literals you can just add a . after the digits:

Query 2:

SELECT 20./1024;

Results:

| COLUMN_0 |
------------
| 0.019531 |

In the case of variables or columns you need to convert one of them using CAST or CONVERT:

Query 3:

SELECT 20/CAST(1024 AS NUMERIC(10,3));

Results:

|   COLUMN_0 |
--------------
| 0.01953125 |

Answer 2

You're using the divide operator to do a integer division.

Quoting the Divide arithmetic operator documentation from Books Online:

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

To get a result with a decimal part, you should first convert one of the operands to a different data type – either FLOAT or NUMERIC work just fine.

For example (SQL Fiddle):

SELECT CAST(CAST(20 AS FLOAT) / 1024 AS NUMERIC(10,3))

Returns 0.02.

Answer 3

Actually I fixed it already, its because I had to cast each integer not the whole thing. So this worked:

SELECT CAST(CAST(20 AS DECIMAL(2,0))/CAST(1024 AS DECIMAL(4,0)) AS DECIMAL(10,3))