CODEWITHSUNDEEP
sqlsql-server
smomotiu
smomotiu

Reputation: 39

Converting varchar to numeric nightmare

I wanted to throw this out to the team because I’m stumped on this one. Any suggestions would be appreciated.

I am loading a table with data from another table and during that process there is a data type change from VARCHAR to NUMERIC. I am getting an error: Error converting data type varchar to numeric.

This error normally indicates that there must be a value in that column that simply cannot be converted to NUM -- Example: ‘Y’. Except in this case there are nothing but numeric values, even though the DB doesn’t think they are.

I have narrowed down the problem columns by CAST’ing one column at a time. What’s odd is I can CAST the problem columns to FLOAT and then to NUMERIC. Which could be a solution but I’m concerned about using FLOAT with currency, I don’t think FLOAT rounds the same a NUMERIC when you have several decimal places. However I could be mistaken and it’s totally fine to use FLOAT.

Just for a test, I tried INT, when I try to CAST AS INT I get a more specific error. Conversion failed when converting the varchar value '1823.33' to data type int.

So when I run the following query it all seems to work…

SELECT     
  [Expended]
  ,CAST([Expended] AS FLOAT) AS Exp_FLOAT
  ,CAST(CAST([Expended] AS FLOAT) AS NUMERIC (18,4)) AS Exp_NUM
FROM [Stage].[BI_FACT_SPENDPLAN_C]
where [Expended] ='1823.33'

Expended      Exp_FLOAT     Exp_NUM
1823.33       1823.33       1823.3300
1823.33       1823.33       1823.3300
1823.33       1823.33       1823.3300
1823.33       1823.33       1823.3300
1823.33       1823.33       1823.3300

I guess what I’m asking is: Do you think I should CAST AS FLOAT and then CAST AS NUMERIC or is there something significant about the following 9 columns in the SPENDPLAN export file? Then it begs the question, if I “fix” these columns with a double CAST could it happen to another column and fail on table load?

SELECT     
    [Expended]       
    ,[Obligated]     
    ,[Total_Expended]      
    ,[Total_Obligated_FAMIS] 
    ,[Spendplan_Balance] 
    ,[Pending_Expenditures] 
    ,[Unexpended_Balance]
    ,[Funds_Remaining_by_BBFY]
    ,[PY_Funds_Remaining]
FROM [Stage].[BI_FACT_SPENDPLAN_C]

Upvotes: 0

Views: 1788

Answers (1)

James
James

Reputation: 3015

Just to be sure...

This perfectly works:

declare @expended varchar(100)='1823.33'
select convert(numeric,@expended)

Are you totally sure that your problem is really related with that record?.

Upvotes: 2

Related Questions