1
select 19/12

return 1,I need in decimal that is 1.58

I am using sqlserver 2005

Ali
  • 3,545
  • 11
  • 44
  • 63
  • select convert(decimal(9,2),19/12.0) or you can multiply with 1.0 when the right part is not a constant select convert(decimal(9,2),19/(12 * 1.0)) – Adrian Iftode Feb 13 '12 at 10:58

5 Answers5

4

You have to do a decimal or float conversion before the CAST. Otherwise it is just integer division

SELECT
   CAST(1.00 * 19 / 12 AS DECIMAL(19,2))

However, the 2 decimal place thing is a presentation issue. I'd do that in the client code

gbn
  • 422,506
  • 82
  • 585
  • 676
1

I'm a MySQL fan, but you could try this:

Maybe SELECT 19.0/12.0 Will give you the answer you're looking for?

Chuck R
  • 731
  • 1
  • 7
  • 15
  • It doesn't give the answer the OP's looking for though. He wants it to two decimal places. So it needs to be converted to a decimal in one way or another. – deutschZuid Feb 13 '12 at 20:48
1

There are a couple of correct answers already.

If you'd like to avoid using cast and convert, define your variables as real, for example. Of if you want to stick to explicit usage of numbers, simply put it this way:

select 19.00/12.00
Laszlo T
  • 1,165
  • 10
  • 22
  • 1
    You'd get decimal (11,7) result because of http://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy/424052#424052 – gbn Feb 13 '12 at 11:53
0

Use CAST.

SELECT CAST((CAST(19 AS DECIMAL(9, 2)) / CAST(12 AS DECIMAL(9,2))) AS DECIMAL(9, 2))

This will convert the result each input number to a DECIMAL and also output the result as a DECIMAL.

The output of the above SQL is:

1.58

Or, simply do as @gbn (the short-method) has put:

SELECT CAST(1.00 * 19 / 12 AS DECIMAL(9, 2))
Neil Knight
  • 47,437
  • 25
  • 129
  • 188
0

actually he only need to cast the numeratior:

select cast(cast(19 as numeric(5,2))/12 as numeric(5,2))
Diego
  • 34,802
  • 21
  • 91
  • 134