SQL divide two integers and get a decimal value error

Question

In an SQL statement, I am trying to divide two integers (integer 1 is "abc" in my code below, integer 2 is "xyz" in my code), and get a result as a decimal (def in my code below). The decimal result should have a leading 1 or 0 only, followed by a decimal and 3 numbers after the decimal. However my code keeps returning a straight 0 with no decimals.

SELECT CONVERT(DECIMAL(4,3), abc/xyz) AS def

This code results in "0", when what I want is something like "0.001" or "0.963". I believe that it is still looking at "def" as an integer, and not as a decimal.

I have also tried using CAST on abc and xyz but it returns the same thing. I have also tried the following code:

SELECT CONVERT(DECIMAL(4,3), abc/xyz) AS CONVERT(DECIMAL(4,3)def)

But this gives me an error, saying there is a syntax error near the word "CONVERT".

asantaballa · Accepted Answer · 2013-11-15T20:48:57.107

6

Convert to decimal before the divide, not after. The convert for answer format.

SELECT 
  CONVERT( DECIMAL(4,3)
         , ( CONVERT(DECIMAL(10,3), abc) / CONVERT(DECIMAL(10,3), xyz) ) 
         ) AS def

edited Nov 15 '13 at 20:48

answered Nov 15 '13 at 18:41

asantaballa

3,919
1
21
22

Then I get the following error - Arithmetic overflow error converting int to data type numeric. – user2762748 Nov 15 '13 at 20:12
Then that means your initial values are larger than DECIMAL(4,3). I should have asked rather than just taking from your initial result, sorry. If the initial fields are integer then they can be 2 billion so make the converts DECIMAL(13,3). But basically make the convert DECIMAL(x,3) where x is large enough to hold your initial integer values. Will edit answer to size large enough for SQL INT datatype. – asantaballa Nov 15 '13 at 20:21
Thanks. If i increase it, then I do get values, but it's still not formatting how I want. If I do Decimal(10,3), then some of my values are as follows: 0.36, 0.3470588235, 0, 0.1, 0.292134831. I would like for the result to always have a decimal and exactly 3 numbers to the right of the decimal. Do I have to do a CONVERT on "def" as well? – user2762748 Nov 15 '13 at 20:30
Cool, we have data. The initial converts were for having correct data. So, yes, if you want to control the format of the output then would "format" it as well with a convert. Edited answer using DECIMAL(4,3) as you had initially but if not large enough can adjust as required. – asantaballa Nov 15 '13 at 20:46
We are getting closer. Now, no more then 3 decimal places are displayed, which is good. But if the last decimal place is a 0, that 0 is being dropped. So, some results are returning as "0.3", when I need "0.300". Also I have a result that is "1", and I need "1.000". Do I have to change something with the "Convert ( Decimal (4,3)" area? – user2762748 Nov 15 '13 at 20:59
Nevermind, I think I have that part figured out. Where I present the data on the page, I wrapped "def" in FormatNumber(def,3) and now it's display how I want. Thank you for your help! – user2762748 Nov 15 '13 at 21:32

score 2 · Answer 2 · answered Nov 15 '13 at 19:19

2

This should do it. Also add divide by zero check.

 SELECT CONVERT(DECIMAL(4,3), abc) / NULLIF(xyz,0)

answered Nov 15 '13 at 19:19

sam yi

4,806
1
29
40

score 0 · Answer 3 · edited Nov 15 '13 at 18:57

0

Try something like

SELECT CONVERT(DECIMAL(4,3), abc/(xyz * 1.0)) AS def

edited Nov 15 '13 at 18:57

Youbaraj Sharma

1,295
3
17
34

answered Nov 15 '13 at 18:37

David

1,591
1
10
22

score 0 · Answer 4 · answered Nov 15 '13 at 18:39

0

Hope this will work...

SELECT CONVERT(DECIMAL(4,3), abc/10.0) AS def
  FROM dbo.whatever;

answered Nov 15 '13 at 18:39

Youbaraj Sharma

1,295
3
17
34

SQL divide two integers and get a decimal value error

4 Answers4

Linked

Related