Date conversion

[ds_team]

I have date comming in flat file. My target is Teradata.In terada the type of the date is timestamp. How to convert the date comming in flat file to timestamp type in the transformer stage.

[I_Server_Whale]

What is the format of the incoming date field? What is the format of a Teradata timestamp? You need provide that information in order to proceed further.

[ds_team]

in the sequential file stage we have kept the type unknown for the incomming date. In teradata table the type is Timestamp with length 22.
We are getting below error .

Teradata_Enterprise_1,0: At field "Effective_Date": Default value not valid for type "timestamp"; text: default="0001-01-01 00:00:00.00"

At field "Expiration_Date": Default value not valid for type "timestamp"; text: default="0001-01-01 00:00:00.00"

We have kept the type as time stamp in transformer stage for target teradata table.
Conversion function used in transformer is
StringToTimestamp(DSLink4.Effective_Date,"%dd/%mm/%yyyy %hh:%nn:%ss")

In sequential file date is comming as
27/12/2004 0:00:00

[ArndW]

PX formats have to match exactly; in your case the time part in the string is "0:00:00" which I think is causing the problem.

[ds_team]

But in the sequential file stage the type for this field is kept as unknown. How can i solve this problem. The date in the file need to be loaded to TERADATA table. Please give me some ideas

[umamahes]

First validate the timestamp field with IsValid("TimeStamp", StringToTimeStamp(DSLink4.TimeStamp,"%dd/%mm/%yyyy %hh:%nn:%ss") )

After that if it is valid time stamp then convert that into time stamp format else put the default time stamp according to your project standard(for ex 9999-12-31 00:00:00)

If StageVar1 then StringToTimestamp(DSLink4.TimeStamp,"%dd/%mm/%yyyy %hh:%nn:%ss") else DSJobStartTimestamp

[ds_team]

Can you please explain it bit more clearly. I am not getting your explanation ..

So Sorry for the trouble

[DSguru2B]

Unknown is the sql type. It can also be varchar, it can also be char. The point that ArndW and umamahes are trying to make is that you need to first check for a valid format. If you find a valid format then change it to timestamp, else you can do something with it. You can possible flag it, reject it etc. etc. etc.

[ray.wurlod]

Look at Appendix A in the Parallel Job Developer's Guide for the data type conversion functions that are available in the Transformer stage. Surely some combination of these will address your question.

You could, as an alternative, use a Modify stage to effect the data type translation.