3

When I run the below query in sql server 2014, the output datatype seems to be different from the input data type

DECLARE @i DECIMAL(18,2) = 2 ,@j DECIMAL(18,2) = 8

SELECT (@i/@j)

Expected Output is : 0.25
But what I'm getting is : 0.25000000000000000000

May I know why this DECIMAL(18,2) is changed?

Joe Samraj
  • 311
  • 3
  • 21
  • Standard behavior for decimal division. – jarlh Sep 15 '16 at 09:36
  • 3
    You might want to read up on [precision, scale and length](https://msdn.microsoft.com/en-GB/library/ms190476.aspx), and specifically how they are defined for results. – Damien_The_Unbeliever Sep 15 '16 at 09:39
  • You may `CAST`, `CONVERT` or need to create a variable with `DECIMAL(18,2)` to store this operation. like `DECLARE @i DECIMAL(18,2) = 2 ,@j DECIMAL(18,2) = 8, @k DECIMAL(18,2); SELECT @k = (@i/@j); SELECT @k;` – Arulkumar Sep 15 '16 at 09:42
  • 1
    And for some of the edge cases you might be interested in, I explored them [here](http://stackoverflow.com/a/25301737/15498) – Damien_The_Unbeliever Sep 15 '16 at 09:47

5 Answers5

1

Try with the below query. 

DECLARE @i DECIMAL(18,2) = 2 ,@j DECIMAL(18,2) = 8

SELECT CAST((@i/@j) as decimal(18,2))
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
1

For SQL-Server 2012+ You can use FORMAT() :

SELECT FORMAT((@i/@j), 'N2')

As @Damien_the_Unbeliever said, you should note that this will return the output as a string..

For lower versions try this:

SELECT cast((@i/@j) as  decimal(18,2))
sagi
  • 40,026
  • 6
  • 59
  • 84
  • Even on SQL Server 2012 and higher, you should generally prefer to stick with appropriate data types rather than converting to string, unless this is *specifically* for presentation purposes. – Damien_The_Unbeliever Sep 15 '16 at 09:41
  • @Damien_The_Unbeliever I've added a comment :) – sagi Sep 15 '16 at 09:44
1

You can try this

SELECT convert(decimal(18,2),(@i/@j))
Kannan Kandasamy
  • 13,405
  • 3
  • 25
  • 38
0

Simply try this:

DECLARE @i DECIMAL(18,2) = 2 ,@j DECIMAL(18,2) = 8
SELECT (@i/CAST(@j AS FLOAT))

OR

DECLARE @i DECIMAL(18,2) = 2 ,@j DECIMAL(18,2) = 8
SELECT CAST((@i/@j) AS FLOAT)
Shushil Bohara
  • 5,556
  • 2
  • 15
  • 32
0

Please refer https://msdn.microsoft.com/en-IN/library/ms190476.aspx

Regarding Scale and Precision of a normal Division operation is as follows

Operation : e1 / e2

Result precision(Pr): p1 - s1 + s2 + max(6, s1 + p2 + 1)

Result scale(Sr): max(6, s1 + p2 + 1)

Where

p1 is precision of e1

s1 is scale of e1

p2 is precision of e1

s2 is scale of e1

In your case p1=p2=18 and s1=s2=2

As per the above calculation

Precision of output (Pr) and Scale of outpur(Sr) will be as follows

Pr = p1 - s1 + s2 + max(6, s1 + p2 + 1)

Pr = 18 - 2 + 2 + max(6, 2 + 18 + 1)

Pr = 18 - 2 + 2 + 21

Pr = 39

Hence there has to be maximum 39 digits in the result. But

  • The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

hence there will be 38 digits.

As division result in decimal value less than one but greater than 0 it will give only one 0 before decimal point

Now Let calculate Scale (Sr)

Sr = max(6, s1 + p2 + 1)

Sr = max(6, 2 + 18 + 1)

Sr = 21

There has to be 21 digits after decimal point. however as per maximum Precision value last digit in scale will be truncated. that's why in you result you have got only 20 digits after decimal point.

Hence the result 0.25000000000000000000

If you want to get 0.25 the round the division using CAST or Convert as follows

DECLARE @i DECIMAL(18,3) = 2 ,@j DECIMAL(18,3) = 8

SELECT CAST((@i/@j) AS Decimal(18,2))
captainsac
  • 2,484
  • 3
  • 27
  • 48