String to Datestamp challenges
Moderators: chulett, rschirm, roy
String to Datestamp challenges
Hello all!
I am attempting to use a Transform stage to convert incoming date as VarChar(100) from one Oracle table to Timestamp(38 ) in another Oracle table.
For example, in_external_Date_Dim.GREG_D = 2006-01-01
I have attempted variations on
StringToTimestamp( trim(in_external_Date_Dim.GREG_D) : " 00:00:00","%yyyy-%mm-%dd %hh:%nn:%ss")
In case it is difficult to see, there is a space between the " and the first 0.
I receive this error in my Director log:
Trans_Date_Dim_datatypes,0: Conversion error calling conversion routine timestamp_from_string data may have been lost
I did not orginally have the Trim function in, but I thought it might be concatenating the " 00:00:00" after 90 or so characters of whitespace.
I was really hoping to find a timestamp function that takes the date and assumes the time part is "%hh:%nn:%ss", but no such luck.
When I was digging through the help, I found Tag.To.Date and Timestamp transforms, but the Transformer stage does not recognize those.
Given my troubles with this, I fear for converting varchar(100) to Julian dates.
TIA!
-Aaron
I am attempting to use a Transform stage to convert incoming date as VarChar(100) from one Oracle table to Timestamp(38 ) in another Oracle table.
For example, in_external_Date_Dim.GREG_D = 2006-01-01
I have attempted variations on
StringToTimestamp( trim(in_external_Date_Dim.GREG_D) : " 00:00:00","%yyyy-%mm-%dd %hh:%nn:%ss")
In case it is difficult to see, there is a space between the " and the first 0.
I receive this error in my Director log:
Trans_Date_Dim_datatypes,0: Conversion error calling conversion routine timestamp_from_string data may have been lost
I did not orginally have the Trim function in, but I thought it might be concatenating the " 00:00:00" after 90 or so characters of whitespace.
I was really hoping to find a timestamp function that takes the date and assumes the time part is "%hh:%nn:%ss", but no such luck.
When I was digging through the help, I found Tag.To.Date and Timestamp transforms, but the Transformer stage does not recognize those.
Given my troubles with this, I fear for converting varchar(100) to Julian dates.
TIA!
-Aaron
<<<>>>
My opinions are just that.
My opinions are just that.
Welcome Aboard
The latter functions that you have mentioned are present only in a Basic Transformer. What is the length of your target stage? What is your target stage? If its not a flat file then try writing to a flat file. I have done this in the past with success.
The latter functions that you have mentioned are present only in a Basic Transformer. What is the length of your target stage? What is your target stage? If its not a flat file then try writing to a flat file. I have done this in the past with success.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Thanks! I like the sig, btw.DSguru2B wrote:What is the length of your target stage? What is your target stage? If its not a flat file then try writing to a flat file. I have done this in the past with success.
Target stage is Oracle, a different table than the source. According to the table defintion, type is timestamp, length is 38.
Good suggestion on the flat file. I still have the same errors in the log, but it processed all the incoming rows. The field I am working on shows up as:
2006-01-01 00:00:00
in the target file.
<<<>>>
My opinions are just that.
My opinions are just that.
How are you pulling the data? Is it already in timestamp format? Are you pulling directly from ORACLE? Analyze its select sql, does it have TO_CHAR() for the date. If yes then you are getting a string and StringToTimestamp() should work. I just tried to recreate your issue at my end, not successful. I am getting a clean run.
With Oracle as your destination, your must be getting an ORA-XXXX error code. Post that error code here. Also, what is your incoming date format?
With Oracle as your destination, your must be getting an ORA-XXXX error code. Post that error code here. Also, what is your incoming date format?
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
No, as that's a perfectly valid timestamp. He posted the issue:
"Trans_Date_Dim_datatypes,0: Conversion error calling conversion routine timestamp_from_string data may have been lost"
I assume that's because you are going from a 100 character string down to a 19 character one. I'm assuming you need to trim or substring it first to avoid this message.
"Trans_Date_Dim_datatypes,0: Conversion error calling conversion routine timestamp_from_string data may have been lost"
I assume that's because you are going from a 100 character string down to a 19 character one. I'm assuming you need to trim or substring it first to avoid this message.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Data is coming from a different Oracle table. Source column data type is VarChar with length 100. SQL code is straight select. Format of column is (string) '2006-01-01'DSguru2B wrote:How are you pulling the data? Is it already in timestamp format? Are you pulling directly from ORACLE? Analyze its select sql, does it have TO_CHAR() for the date. If yes then you are getting a string and StringToTimestamp() should work. Post that error code here. Also, what is your incoming date format?
Oracle Errors per Director log:
[Ora_Date_Dim is Oracle stage, Upsert mode]
Ora_Date_Dim,0: Failure during execution of operator logic. <- First fatal error
Ora_Date_Dim,0: Fatal Error: Invalid Julian day
node_node1: operator [Ora_Date_Dim], partition 0 of 1, processID 28584 on node1, player 3 terminated unexpectedly.
main_program: Unexpected exit status 1
Then a series of Fatal errors in Transformer Stage.
Bah! Am I chasing the wrong bug? Is it possible all of the Warnings are not the problem and it really is the Julain date field?
<<<>>>
My opinions are just that.
My opinions are just that.
Field format
Can you tell me what is the field format of the source and what is the field format of the target?
It might just be putting the function in a wrong way....
It might just be putting the function in a wrong way....
Regards,
Novneet Jain
Novneet Jain
Let me see if I can clarify and answer the above three questions. And again, thanks!
All that being said, I get the TimeStamp warnings when I output to Flatfile. However, the Julian Date error does not appear, and the code completes.
Also, I have gone back through the log (in Director) and I do not see an ORA-NNNN error code listed.
The Timestamp Warning applies to 11 of the 48 fields I am converting to Timestamps. There are also about 10 Julian Date fields, defined as Integer as the Target column.chulett wrote:You don't have any kind of Julian date. Why is that particular error being reported?
The second input row, also varchar (length=100) is to be transformed to a Julian date field defined as Integer (no length specified). Although, I do not quite understand the Julian Date error (Ora_Date_Dim,0: Fatal Error: Invalid Julian day 0) as the datatype is Integer. Hence my comment that I might be chasing the wrong bug (the Timestamp warnings).DSguru2B wrote:Yea, the Julian Day error is odd. As Craig mentioned, do a LEFT(in.Col, 10) and then do StringToTimestamp() on that. Load it to a flat file and see what happens?
All source data is from and Oracle table and datatype VarChar, length = 100. Target (post Transform) is Oracle with datatypes including Timestamp (length=38 ), Integer, and VarChar (various lengths).novneet wrote:Can you tell me what is the field format of the source and what is the field format of the target?
All that being said, I get the TimeStamp warnings when I output to Flatfile. However, the Julian Date error does not appear, and the code completes.
Also, I have gone back through the log (in Director) and I do not see an ORA-NNNN error code listed.
<<<>>>
My opinions are just that.
My opinions are just that.
I have gone insane
I just updated all 11 Timestamp Transforms to be:
StringToTimestamp(inlink.columnName: " 00:00:00")
And all of the errors and warnings went away. The Timestamp warnings, the Julian Date errors, all gone. Data loaded into Oracle successfully.
I am so very confused.
Again, thanks to all who responded!
StringToTimestamp(inlink.columnName: " 00:00:00")
And all of the errors and warnings went away. The Timestamp warnings, the Julian Date errors, all gone. Data loaded into Oracle successfully.
I am so very confused.
Again, thanks to all who responded!
<<<>>>
My opinions are just that.
My opinions are just that.