Take the 2-minute tour ×

In sql, I have col1 and col2. Both are integers.

I want to do like..

select col1/col2 from tbl1

I get the result 1 where col1=3 and col2=2

The result I want is 1.1

I put round(col1/col2,2). The result is still 1.

I putdecimal(col1/col2,2). The decimal is not built in function.

How can I do exactly to get 1.1?

share|improve this question
is it 3 divided by 2 equal 1.5 ? –  bensiu Jan 29 '11 at 3:36
Yes. it is.. .. –  william Jan 29 '11 at 3:37
What kind of rounding gives you 3/2 = 1.1? –  Blorgbeard Jan 29 '11 at 3:48
@Blorgbeard: He might have automatically converted it to binary. :) –  Andriy M Dec 11 '13 at 21:07

4 Answers 4

up vote 4 down vote accepted

You will need to cast or convert the values to decimal before division. Take a look at this http://msdn.microsoft.com/en-us/library/aa226054.aspx

For example

DECLARE @num1 int = 3 DECLARE @num2 int = 2

SELECT @num1/@num2

SELECT @num1/CONVERT(decimal(4,2), @num2)

The first SELECT will result in what you're seeing while the second SELECT will have the correct answer 1.500000

share|improve this answer

SELECT CAST (col1 as float) / col2 FROM tbl1

One cast should work. ("Less is more.")

From Books Online:

Returns the data type of the argument with the higher precedence. For more information about data type precedence, see Data Type Precedence (Transact-SQL).


share|improve this answer

Just another approach:

SELECT col1 * 1.0 / col2 FROM tbl1

Multiplying by 1.0 turns an integer into a float numeric(13,1) and so works like a typecast, but most probably it is slower than that.

Advantages: less wordy.

share|improve this answer
+1 nice approach ;) –  Somebody Dec 19 '12 at 22:13
+100500;) Also possible to use without zero SELECT col1 * 1. / col2 FROM tbl1 –  Alexander Fedorenko May 28 '13 at 16:12
+1 Much easier approach –  Randell McGlynn Apr 15 '14 at 1:06
+1 Excellent tip! –  ccorrin Sep 4 '14 at 20:37

There may be other ways to get your desired result.

Declare @a int
Declare @b int
SET @a = 3
SET @b=2
SELECT cast((cast(@a as float)/ cast(@b as float)) as float)
share|improve this answer

Your Answer


