Error while loading Teradata

[raviyn]

I am trying to load a flat file into the Teradata using MLOAD Plugin . I am getting the following
error 5407: Invalid operation on an ANSI Datetime, in the Log file(report file ).

The i/p has 4 cols out of which 3 are key cols and out of these key columns 1 is a timestamp(6) format in teradata.While defining the metadata for this col I have given CHAR(26) in Datastage.This Col is there in the UPDATE statement in the Where Clause.

If I don't have the timestamp column in the Where clause and instead have it in SET condition of the Update statement then it goes through fine without any errors.

Any Ideas??

[richdhan]

Hi,

The default format in which teradata accepts date is
'yyyy-mm-ddBhh:mi:ss.ssssss'

Try the following in the where clause

cast (column-name as timestamp(6) format 'dd-mm-yyyyBhh:mi:ss.s(6)').

HTH
--Rich

[raviyn]

Thanks for the response.

The data is already coming in yyyy-mm-dd hh:MI:ss.s(6) format from the I/P . So do we need to still use tha Format syntax as you had Given ?

Though I changed it and run I got the same problem. What else do you think should I be looking at the teradata end.

[richdhan]

Hi,

What is the client tool you are using for Teradata.

For e.g We are using Teradata SQL Assistant and I tried to update a single record using the same scenario you had explained.

update TEST_TBL set IS_PRIMARY_FLAG='N' where
CREATE_DTS='2004-06-29 09:19:47.000000'

I got the following error
Code = 5407.
5407: Invalid operation on an ANSI Datetime or Interval value.

I used the method I had proposed in my previous post and it works fine.

update TEST_TBL set IS_PRIMARY_FLAG='N' where CREATE_DTS=cast('2004-06-29 09:19:47.000000' as Timestamp(6))

Try updating a single row in that table and find the response. If this works then in the MLOAD you can probably use the following syntax.

HTH
--Rich