• Register
0 votes
36 views

I received an error message when converting data type varchar to numeric

I imported data into VARCHAR column to check correct character types before moving into the destination table. I ran across some decimal values they could not CAST or CONVERT even though they appeared decimal values. I received the message "Error converting data type varchar to numeric.” How can I load these problems values?
1.9k points

Please log in or register to answer this question.

1 Answer

0 votes

I received an error message when converting data type varchar to numeric

To solve the conversion error, you need to remove the comma (,) from the varchar value you want to convert to numeric.

It would help if you also ensured the value varchar to be converted is the number you need to convert to the numeric data type. You need to check that you use only the decimal symbol, the dot, and not any digit grouping symbols. 

When you need to convert varchar values to numbers (i.e., decimal, numeric, etc.), you need to be careful with your varchar value. It does not need to include any digit grouping symbols (i.e., a comma) or other characters that do not have a meaning as a number.

 The error happens when adding a string and a numeric value. More likely, you must use:

insert into #Tmp
SELECT 1,'TDS @'+CAST(T1.rate AS VARCHAR(30)) + ' %' 
FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.AbsEntry 

It would help if you converted the rate to a string before adding it to another string. Though you also have the FORMAT function depending on your SQL Server, you need to avoid doing that on the SQL Server and instead serve "raw" data that are formatted on the client-side as you need:

-  it is easier to support multiple languages and the appropriate separator- you can apply new rules any time, such as showing the rate using a red color if too low or too high.

I hope it helps!

3.4k points

Related questions

1 vote
1 answer 26 views
26 views
Problem: Need solution of this error; Error converting data type varchar to numeric in sql server.
asked Apr 25 PkGuy 25.9k points
0 votes
1 answer 22 views
22 views
Problem: I need help with it > Error converting data type varchar to numeric.in sql server.
asked May 8 Irenka Kiri 85.5k points
0 votes
1 answer 456 views
456 views
Error converting data type varchar to numeric I imported data into VARCHAR columns to check valid character types before moving into the final table. Even the values appear decimal values, they would not CAST or Convert. The values failed the ISNUMERIC function even if they look numeric. How can I load these values correctly?
asked Nov 2, 2020 miki 1.9k points
1 vote
1 answer 28 views
28 views
Problem: I have an error which is thrown by SQL Server Management Studio when running this code: Sql arithmetic overflow error converting numeric to data type numeric.
asked May 2 PkGuy 25.9k points
0 votes
1 answer 20 views
20 views
Problem: How do I solve this issue : Error converting data type varchar to numeric. in sql server?
asked Jun 22 akhi 106k points
1 vote
1 answer 383 views
383 views
Problem: I am having good exposure to SQL. Currently I want to insert my data from one table to the another table but because of some unknown reason to me I am facing some sorts of issue it shows me below error: Numeric value out of range: 8115 [Microsoft][ODBC ... ,'') AS [TRIM(IngredCost)]  FROM file_5979bd211a3a9` Please help me with this issue. I want to insert all the records in one go.
asked May 23, 2020 Martin K 6.6k points
1 vote
1 answer 371 views
371 views
Problem: I have very basic knowledge in Sql query writing. I have tried to write the below query. SELECT '$'        + CONVERT(VARCHAR (6), Cast(Avg(TotalPrice) AS NUMERIC (6, 2))) AS 'Average Price',        '$'        + CONVERT(VARCHAR (6), Cast(Min( ... converting numeric to data type varchar". I am unable to find the root cause of above error. Kindly help me in fixing my above sql query.
asked May 23, 2020 Martin K 6.6k points
0 votes
1 answer 41 views
41 views
I’m getting error converting data type varchar to numeric sql, how can I solve it?
asked Dec 7, 2020 TeamScript 13.5k points
0 votes
1 answer 41 views
41 views
Problem: Is there some way to solve this? > Error converting data type varchar to numeric sql server .
asked May 10 Isac Christiaan 96.6k points
0 votes
1 answer 20 views
20 views
Problem: I can't find a good solution on internet : Error converting data type varchar to int.
asked Jun 1 Irenka Kiri 85.5k points