Error converting data type varchar to numeric.

Question

1.00/5 (1 vote)

See more:

Hi Everyone,

I have a column [Weight] whose datatype is varchar(50) now i want to alter the column in decimal(10,2).I try this .

ALTER TABLE MAWB
ALTER COLUMN [Weight] decimal(10,2)
I always get the error: Error converting data type varchar to numeric.

Please let me know why do i get this error.

thanks in advance
Harshal.

Posted 17-Aug-14 21:43pm

R Harshal

Add a Solution

Comments

Sergey Alexandrovich Kryukov 18-Aug-14 3:45am

If you need numeric data, use attributes of numeric types, not varchar.
—SA

Wombaticus 18-Aug-14 3:46am

Check your data integrity - perhapos set all empty string values to 0 first.

R Harshal 18-Aug-14 3:48am

Sorry to say ,I did not get it .
what exactly i need to do ??

Wombaticus 18-Aug-14 4:12am

execute
UPDATE MAWB SET Weight = '0' WHERE Weight = ''
before your ALTER statement.

Magic Wonder 18-Aug-14 3:49am

Check your table's column data should not contain VARCHAR value.

R Harshal 18-Aug-14 3:57am

In my table's column[Weight] data is blank.

Magic Wonder 18-Aug-14 3:59am

update all blanks with 0.

2 solutions

Solution 1

This is a good idea, but...your table data is already corrupt.
At least one row in your DB contains non-numeric data and SQL can't convert it. Until that is fixed, you can't alter the column datatype, because SQL doesn't know what to do with the row, and it doesn't want to lose you data.

So, throw together some quick test code in C# or similar to read every row, and report to you which ones can't be converted to numeric values. Then you can fix the rows manually, and change your DB.

Posted 17-Aug-14 22:03pm

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Magic Wonder · Accepted Answer · 2014-08-17T22:09:00

Hi,

You can achieve your task as listed below.

1. as you are doing,

SQL

ALTER TABLE tableName ALTER COLUMN [colName] (datatype)

provided that your data should be of required data type.

2. follow below steps

a. Add another column with required dataType.
b. import all data from old column to new column with dataType check.
c. delete / drop old column with the help of query / management studio once you imported all your required data.
d. Rename your new column as per requirement.

3. Recreate New Table with required table structure, import all required data, drop old table, rename new table as require.

Choice is yours.

Cheers