DSXchange

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

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

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.

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