TPT - Timestamp format error 6760

Tools

TPT - Timestamp format error 6760

I have a simple load step

DEFINE JOB Run_Load
(
Step Load_Tables
(
APPLY $INSERT TO OPERATOR
(
$LOAD()
ATTRIBUTES
(
TargetTable = @TargetDatabase || '.' || @TargetTable,
LogTable = @TargetDatabase || '.' || @TargetTable || '_LOG',
ErrorTable1 = @TargetDatabase || '.' || @TargetTable || '_E1',
ErrorTable2 = @TargetDatabase || '.' || @TargetTable || '_E2'
)
)
SELECT * FROM OPERATOR($FILE_READER( DELIMITED @TargetDatabase || '.' || @TargetTable ) );
);
);

If I have understood correctly, the schema will be inferred from the target table

This is working fine when I have a table defined as:

CREATE MULTISET TABLE MYDATABASE.MYTABLE

     (   cola TIMESTAMP(6) FORMAT 'YYYY/MM/DDbHH:MI:SS')

and data:

2015/09/09 06:24:02

BUT, this is not the format my data is coming in.  What I really want to get working is for data such as:

08/09/2015 06:24:02

So, I am using this table structure:

CREATE MULTISET TABLE MYDATABASE.MYTABLE      

(       cola TIMESTAMP(6) FORMAT 'DD/MM/YYYYbHH:MI:SS')



However, I then get an error 6760.  I don't understand why?  Isn't the date format being taken from the table DDL?  This approach seems to have worked ok with dates previously.

3 REPLIES
Teradata Employee

Re: TPT - Timestamp format error 6760

Please explain what you mean by:

"This approach seems to have worked ok with dates previously."

By "previously", do you mean TPT used to work and now does not?

BTW, when we say we can infer the schema from a table, this means we take the column data types.

We do not look at the format.

That is resolved by the DBS.

Why did you define the column as TIMESTAMP(6) and not TIMESTAMP(0)?

Your data has no fractional seconds?

-- SteveF

Re: TPT - Timestamp format error 6760

Kindly refer the below link for TPT script details: https://teradataworld.wordpress.com/teradata-parallel-transporter-tpt/ 

The data is moved by a date datatype , the format is just for representation., What i can understand is your data is in YYYYmm/dd and you need to load data in dd/mm/yyyy format, in such scenario you need to export and import , once data is loaded in the table it will be represented in the format you have specified.

Re: TPT - Timestamp format error 6760

Hi Steve - by 'previously' I meant I thought it was working this way; obviously it never did, but as you say the DBS was being helpful by handling the date formats I sent anyway.  I understand now, thank you.

I've reverted to having to generate the schema of the producer file myself, so I can specfy the date format like:

VARDATE(20) FORMATIN ('ddMMMyyyy') FORMATOUT ('YYYY-MM-DD')

Thanks for advice