Bad format or character?

UDA
N/A

Bad format or character?

I am uploading some SAS files into TD, trying to convert string to decimal.

I use

(case when myvar is null then -999 else cast( myvar as decimal(18,0)) ) as id

But TD gives me an error, saying the column might contains bad character. Why this happened?
5 REPLIES
Enthusiast

Re: Bad format or character?

Is myvar column of varchar data type?

If myvar is varchar data type then you might get this error "bad character in format or data" with error code 2621.
This happens because, column being a varchar may contain character values also which cant be converted to decimal/integer by using cast. In this case you have to take care of char values in myvar column.

hope this solves your problem.
Please let know in case of wrong assumption.

Re: Bad format or character?

Hi all,

I am getting the same errror. My scenario is i have a field called source_system_code in a table of char '1' and not null. I am trying to insert a value 'V' into that. It is giving be error code 2621 saying bad character format in the field. Kindly help in this.

Thanks,
Vanathi
Enthusiast

Re: Bad format or character?

Hi Vanathi,

Can u please provide some more details as its working fine in my system

Thanks
Enthusiast

Re: Bad format or character?

Hi Papaya,

Looking at your query, i am sure that your column is containing some non integer values thats why it is unable to convert it to decimal and giving u a error.
Fan

Re: Bad format or character?

I'm in the same situation .

Value

-13 --- characters length should be 3  but i get 13 space characters so i cast(substr( MaxTemp,1,3) as char(3)). now i need to convvert the char(3) to integer

My script:

cast(cast(substr( MaxTemp,1,3) as char(3)) as integer) but i get an errror 

Failed 2621 bad character in format or data of table