All Forums Database
SCHINN 11 posts Joined 09/12
08 Apr 2013
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

 

Thanks...
SCHINN 11 posts Joined 09/12
08 Apr 2013

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

Thanks...

08 Apr 2013

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

SCHINN 11 posts Joined 09/12
09 Apr 2013

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....
 
 

Thanks...

SCHINN 11 posts Joined 09/12
09 Apr 2013

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

Thanks...

barani_sachin 141 posts Joined 01/12
09 Apr 2013

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. 
 

KS42982 137 posts Joined 12/12
10 Apr 2013

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.

barani_sachin 141 posts Joined 01/12
11 Apr 2013

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 );

 

dnoeth 4628 posts Joined 11/04
11 Apr 2013

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

Dieter

barani_sachin 141 posts Joined 01/12
11 Apr 2013

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

mandeep_m91 5 posts Joined 05/13
16 May 2013

when you are dealing with char/varchar columns and trying to convert them to numbers, you might try the below to filter out alpha-numeral values
 
select *
from table_name
where upper(column) <> lower(column)
 
Where "column" is of the type char/varchar which you want to convert to integer data type.

You must sign in to leave a comment.