2

I am trying to convert a column formatted in varchar to decimal(19,12) with the following line of code

ALTER TABLE [tablename]
ALTER COLUMN [columnname][format]

and get the following prompt:

Msg 8114, Level 16, State 5, Line 25
Error converting data type varchar to numeric.

Has worked before like a charm. The issue here seems to be that the values in the column are 19 or so digit numeric values formatted as text.

I tried to create a new column, pasted shortened cell values (used the left() function) into it from the original column but that doesn't seem to do the trick either since the code above ends up occationally with the additional "Arithmetic overflow occurred." message.

Improve this question
3
  • are there more than 7 digits to the left of the decimal in any row? or more than 12 to the right? Commented Sep 21, 2017 at 14:02
  • The tsql tag suggests sql server, what version do you use? Commented Sep 21, 2017 at 14:03
  • I am on an Azure SQL Database. (19,12) is in our case appropriate, but isn't the issue here that the string data about to be converted into numberic data exceeds the 19,12 range rather than the other way around? Commented Sep 21, 2017 at 15:58

2 Answers 2

Reset to default
1

When some of the rows have incorrect values, ALTER COLUMN would not work. A typical course of action goes like this:

  1. Add a new column of the desired type
  2. Update the column with values that you would like to keep
  3. Drop the old column
  4. Rename the new column

Step 2 would go like this:

UPDATE MyTable
SET NewColumn =
    CASE WHEN ISNUMERIC(OldColumn)=1 AND DATALENGTH(OldColumn) <= 19 THEN
        CAST(OldColumn AS decimal(19,12))
    ELSE
        NULL
    END

You could also turn ANSI warnings off with SET ANSI_WARNINGS OFF command, which would let you run ALTER COLUMN ignoring data trunction errors. The drawback of this approach is that potential errors get ignored. On the other hand, when you do conversion explicitly with a CASE expression you have an option to supply an alternative value for the error case (I used NULL above, but you can put any number you want).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

How would you go about turning the ANSI warning off in regards to actual code? What are the drawbacks?
0

Could you try to seperate your problem? This does work on SQL 2012:

set nocount on 
if object_id ('tempdb..#t1') is not null drop table #t1

create table #t1 (c1 varchar(100))

insert #t1 values ('1234567.8901234567890')
select * from #t1

alter table #t1
alter column c1 decimal(19,12) 

select * from #t1

If you play around a bit with the strings you easily can produce an arimetic overflow error. But 'Error converting data type varchar to numeric' needs character or empty sting.

Maybe you can try with your data?

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.