Cast from CHAR(20) to SMALLINT

Database
Enthusiast

Cast from CHAR(20) to SMALLINT

Hello Friends.

I am trying to convert a column havign CHAR(20) to SMALLINT and getting the below error....

I have the below distinct values....as char(20).

this query Cast(cast(red_Cd as CHAR(3)) AS SMALLINT) and getting this error.

Bad character in date or format..you help is appreciated..thanks...




?
-1
100
101
102
104
106
110
111
112
119
204
205
211
300
302
303
304
306
307
309
310
311
313
315
316
500
10 REPLIES
Enthusiast

Re: Cast from CHAR(20) to SMALLINT

Friends...Any help on this would be appreciated..

Thanks..

Teradata Employee

Re: Cast from CHAR(20) to SMALLINT

Hello Sachin,

I tried the syntax provided by you and it worked for me:

sel name,cast (cast(name as char(6)) as smallint) from delta

Regards,

Chinmay Athavale

Enthusiast

Re: Cast from CHAR(20) to SMALLINT

Hello Chinmay,

originally that column is char(20) trying to change to smallint.

Do you think the data i have above might be causing any issue?

Appreciate your help....

Enthusiast

Re: Cast from CHAR(20) to SMALLINT

i figured out the problem..there was a problem with data in that column

Enthusiast

Re: Cast from CHAR(20) to SMALLINT

What is the problem?? I am also getting an issue: Able to insert till 119 after that i am getting 

 INSERT Failed. 3520:  A constant value in a query is not valid for column col1. 

Enthusiast

Re: Cast from CHAR(20) to SMALLINT

It should not throw any errors if you are inserting the records as per the first post. Can you share the query that is giving you the problem and will look into it.

Enthusiast

Re: Cast from CHAR(20) to SMALLINT

Hi KS,

Below is the insert stmt.

create volatile table temp1 (col1 byteint)on commit preserve rows;

insert into temp1 values( -1 );
insert into temp1 values( 100 );
insert into temp1 values( 101 );
insert into temp1 values( 102 );
insert into temp1 values( 104 );
insert into temp1 values( 106 );
insert into temp1 values( 110 );
insert into temp1 values( 111 );
insert into temp1 values( 112 );
insert into temp1 values( 119 );
insert into temp1 values(204);
insert into temp1 values( 205 );
insert into temp1 values( 211 );
insert into temp1 values( 300 );
insert into temp1 values( 302 );
insert into temp1 values( 303 );
insert into temp1 values( 304 );
insert into temp1 values( 306 );
insert into temp1 values( 307 );
insert into temp1 values( 309 );
insert into temp1 values( 310 );
insert into temp1 values( 311 );
insert into temp1 values( 313 );
insert into temp1 values( 315 );
insert into temp1 values( 316 );
insert into temp1 values( 500 );
Junior Contributor

Re: Cast from CHAR(20) to SMALLINT

The range of a byteint is -128 to +127, change the datatype to SMALLINT.

Dieter

Enthusiast

Re: Cast from CHAR(20) to SMALLINT

Sorry guys, ma bad. Thanks for u r time :)