CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user3383390
user3383390

Reputation: 211

Converting Varchar to decimal

I am having two fields holding data as varchar

field1= 10,000.00 and field2= 5,000.00

I now need to add these two fields to find sum . But when I add as Field1+Field2 the

result is 10,000.005,000.oo

I tried to use CAST(Field1 as decimal(12,2)) but it throws a error that it cannot convert varchar to numeric.

Help me out.! 

                        SELECT     [1] AS ContractPriceExcluded, [2] AS Anticipated, [3] AS Approved, [4] AS claims, [5] AS ProvisionalSums, [6] AS CommitmentsToDate, [7] AS AnticipatedFCost, 
                  [8] AS VarianceToContract, [9] AS CertifiedPayments,[10] AS ContractPriceIncluded , [11] AS ProvisionalSumsNotactivated ,description, status, ReferenceNo
                    FROM         (SELECT     ISNULL([Workflow_CustomFormfields].[fieldnumber], '') AS fieldnumber, 
                                         CAST( REPLACE([Workflow_CustomFormFields].fieldvalue,',','')as Decimal(12,2))  AS fieldvalue, isnull(workflow_customforms.subject, '') AS description, 
                                          isnull(workflow_customforms.categoryvalue, '') AS status, isnull(workflow_customforms.reference, '') AS ReferenceNo
                   FROM          [dbo].[Workflow_CustomFormfields] INNER JOIN
                                          dbo.Workflow_CustomForms ON [dbo].[Workflow_CustomForms].[Id] = dbo.Workflow_CustomFormfields.customformId
                   WHERE      dbo.Workflow_CustomForms.ProjectId = 1 AND dbo.Workflow_CustomForms.CustomformtypeId = 75) PS PIVOT (max(fieldvalue) FOR fieldnumber IN ([1], [2], 
                  [3], [4], [5], [6], [7], [8], [9],[10],[11])) AS pivot_fieldvalue

Upvotes: 0

Views: 9802

Answers (3)

Shafeeq Koorimannil
Shafeeq Koorimannil

Reputation: 724

You have to remove the comma and convert to decimal. See below.

declare @field1 varchar(50) = '10,000.00' ,
 @field2 varchar(50)= '5,000.00'

select Convert(decimal(10,2),replace(@field1,',','')) +Convert(decimal(10,2),replace(@field2,',',''))

Upvotes: 0

Raj
Raj

Reputation: 10853

Are you casting both values as decimal? This works fine

DECLARE @one varchar(8), @two varchar(8)
SET @one = '10,000.00'
SET @two = '5,000.00'
SELECT CAST(replace(@one,',','') AS decimal(12,2))+
       CAST(replace(@two,',','') AS decimal(12,2))

Upvotes: 0

MikkaRin
MikkaRin

Reputation: 3084

 select cast(replace('10,000.00',',','') as decimal(12,2))

The problem is in comma

Upvotes: 4

Related Questions