Hi everyone, i need load some data from a csv file to a teradata table using TPT.
I´m having some troubles on a field what has the following mask in the table: DD/MM/YYYYBHH:MI
The problem its here: In csv file the field has data like this: 04/04/2016 16:38, this sample has the correct mask but on the other hand, data what has hour between 0:00 and 9:59 not uses the correct mask, an example 04/04/2016 9:30, uses DD/MM/YYYYBH:MI and tpt detects this like an error.
I need some tip to put a 0 before the hour if the hour its between 0:00 and 9:59.
I think uses case and compare the field length, 16 for the correct date and 15 for the error date, compare and put a 0 before the hour in 15 length field on the mask, to make this mask DD/MM/YYYYB0H:MI to transform error date before tpt try to insert in the table
Thanks ind advance and sorry for my english T.T
You can do it easily using FORMATIN clause for VARDATE type in the schema definition.
FORMATIN ('DD/MM/YYYYbhh:mi') will accept the one digit hours.
Hi Carlos thanks for your help
I didnt use before vardate to insert data, i need a formatout clause
I was using this to insert my timestamp data on Apply zone:
Thanks in advance
DEFINE SCHEMA your_schema
your_timestamp VARDATE FORMATIN('DD/MM/YYYYbhh:mi') FORMATOUT('YYYY-MM-DDbhh:mi:ss')
I think the mask what you give me on the Formatout clause its the native mask on TD.
There´s not problem.
Using this tip what you give me TPT have the following error:
TPT10679 Error: MONTH value supplied in data row does not fall into supported range [1,12].
It's strange because i checked all data and i didnt see any month what is not between 1 -12
I checked data using an excel filter at the column and only shows me Year => April, no other data, so every date is correct.
There´s no problem, we choose another option, we make a temp table of full varchar and insert there all the data.
Finally with a procedure, we format correctly the data to insert them on final table.
Thanks for all