sql code 1403 and records getting rejected..

allavivek · Post by **allavivek** » Wed Nov 10, 2010 4:12 pm

Hi All,

My source is having varchar of "2010/11/31" format and my target is column having timestamp ...database on target is oracle...

Iam using stringtodate(col,'%yyyy/%mm/%dd') and all records are geting rejected giving 1403 as sql code..

As target is timestamp i used
if IsNotNull(col) or Trim(col)<>'' then Stringtotimestamp((col:" 00:00:00"),"%yyyy/%mm/%dd %hh:mm:ss") else SetNull()

Another method used for left input as string with col:" 00:00:00" and in insert query i used
to_date(orchestrate.col,'YYYY/MM/DD HH:MI:SS')
its giving error as invalid month...

I was getting error as invalid time...

Any suggestions how to make it work....

Thank you..

chulett · Post by **chulett** » Wed Nov 10, 2010 9:01 pm

"%yyyy/%mm/%dd %hh:%mm:%ss"

allavivek · Post by **allavivek** » Wed Nov 10, 2010 9:06 pm

chulett wrote:"%yyyy/%mm/%dd %hh:%mm:%ss"

i printed wrong....thts wht i gave...

chulett , If the target is timestamp data type and if iam inserting date will oracle database accepts or throws records...

One more doubt is if iam having 2010/11/11 00:00:00 will the length changes in datastage and oralce..or will be same in both??

Thank you...

chulett · Post by **chulett** » Wed Nov 10, 2010 9:26 pm

What is the Oracle column's type? While the job can use a Timestamp in either case, things are different when populating a DATE versus a TIMESTAMP.

allavivek · Post by **allavivek** » Thu Nov 11, 2010 6:27 am

chulett wrote:What is the Oracle column's type? While the job can use a Timestamp in either case, things are different when populating a DATE versus a TIMESTAMP. ...

Oracle column type is Timestamp......

chulett · Post by **chulett** » Thu Nov 11, 2010 6:30 am

Then you'll need to ensure you pass in a full timestamp value, not just 'a date'. Of course, that timestamp can contain a completely zero time portion, including any fractional seconds (if needed).

allavivek · Post by **allavivek** » Thu Nov 11, 2010 7:04 am

chulett wrote:Then you'll need to ensure you pass in a full timestamp value, not just 'a date'. Of course, that timestamp can contain a completely zero time portion, including any fractional seconds (if needed). ...

i did but records are getting rejected again with 1403 sql code..

Iam having a 'NULL' data column where iam inserting in to target column having nullable property yes' and having type char(1)...

are records getting rejected due to this...orcould be any other reason...because i see the constraints are equal in both source nd target,....

Thank you...