Decimal places for integer divisions

SELECT 1/2 returns 0 in transact sql instead of 0.500000
How do I get it to return 0.5000 ?You need to force SQL Server to not do integer division. Try:

SELECT CAST(1 AS float) / CAST(2 AS float)|||You really have to understand datatypes. I still regularly get questions that resemble this one from developers who've been using SQL for years.

In integer division, one divided by two equals zero with a remainder of one. When you divide one integer by another, integer math is used. If you cast either the dividend or the divisor to a real, then real math will be used (with implicit promotion if needed).

-PatP|||Originally posted by calicocat
You need to force SQL Server to not do integer division. Try:

SELECT CAST(1 AS float) / CAST(2 AS float)

Nah...it'll infer type if you let it...

SELECT 1/2.0|||A little more on my problem.

I have two columns in a table each storing integer values. I would like the division to return a decimal number

eg. select col1/col2 should return a decimal result... Neither of the above solutions work.|||OK, so now you need to convert

USE Northwind
GO

CREATE TABLE myTable99(Col1 int, Col2 int)
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,2
GO

SELECT Col1/CONVERT(decimal(8,4),Col2)
FROM myTable99
GO

DROP TABLE myTable99
GO