Date conversion
Moderators: chulett, rschirm, roy
Date conversion
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.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Error while loading date to TERADATA table
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
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
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hi
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
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
HI
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
You could, as an alternative, use a Modify stage to effect the data type translation.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.