CODEWITHSUNDEEP
sql-serversql-server-2012
Sandy
Sandy

Reputation: 275

Decimal division precision

Earlier, the result set shows 300.00 when it was as below where addition is bein taking place from two different table colums.

 Cast((SELECT Cost  FROM Table1 WHERE Id = CD.Id) + 
      (SELECT RecuritmentCost FROM Table2 WHERE Id = CD.Id)
 AS VARCHAR(10))

But I have got some requirement to do a division operation in one of the case so within case statement I am using below which is resulting into 300.0000000000000

cast((((
    SELECT Cost  
    FROM Table1 
    WHERE Id = CD.Id
    ) + (
    SELECT RecuritmentCost
    FROM Table2 
    WHERE Id = CD.Id
    ))/(Select Count(*) from tblEmployee where Id = CD.Id and emptype=2)) AS VARCHAR(18))

here i have used varchar(18) because it was throwing error if i use varchar(10)

Error was "Arithmetic overflow error converting numeric to data type varchar".

by using varchar(18) , this error was removed but result start showing 300.0000000000000

The colums on which operations are taking place are of type

Cost(decimal(18,2),null)
RecuritmentCost(decimal(18,2), null)

I think I am not converting the things properly but actually I need result to be in a string, so I am using Cast as Varchar(18). Can we solve this problem that if any operation takes place on these two columns, it should return result in two decimal places and thats too in string format.

Upvotes: 0

Views: 322

Answers (1)

Tanner
Tanner

Reputation: 22733

Firstly, when you do arithmetic on values with different data types, SQL Server will pick what it believes the best data types are for the result. Your query is adding 2 decimal values and then dividing by an integer (the count(*)). You're then casting it to a varchar. The division of the result of the subquery is what is leading the results to change type.

I'm not sure why you are doing the cast to varchar, when you can simply cast the result to decimal(18,2) to maintain the precision. If you do need the value as a varchar, then cast it to decimal(18,2) first then to varchar:

Run the below tests:

DECLARE @decimalValue DECIMAL(18, 2) = 300.00;

SELECT @decimalValue;
-- 300.00

SELECT CAST(@decimalValue + @decimalValue 
            / ( SELECT 3 ) AS VARCHAR(18)) AS ResultOriginal;
-- 400.0000000000000

SELECT CAST(@decimalValue + @decimalValue 
            / ( SELECT 3 ) AS DECIMAL(18,2)) AS ResultAsDecimal;
-- 400.00

SELECT CAST(CAST(@decimalValue + @decimalValue 
            / ( SELECT 3 ) AS DECIMAL(18,2)) AS VARCHAR(18)) AS ResultAsVarchar;
-- 400.00

Upvotes: 1

Related Questions