String to Datestamp challenges

AaronVG · Post by **AaronVG** » Thu Feb 22, 2007 3:38 pm

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

DSguru2B · Post by **DSguru2B** » Thu Feb 22, 2007 3:43 pm

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.

AaronVG · Post by **AaronVG** » Thu Feb 22, 2007 4:03 pm

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.

Thanks! I like the sig, btw.

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.

DSguru2B · Post by **DSguru2B** » Thu Feb 22, 2007 4:24 pm

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?

chulett · Post by **chulett** » Thu Feb 22, 2007 4:36 pm

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.

AaronVG · Post by **AaronVG** » Thu Feb 22, 2007 4:37 pm

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?

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'

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?

chulett · Post by **chulett** » Thu Feb 22, 2007 5:05 pm

You don't have any kind of Julian date. Why is that particular error being reported?

DSguru2B · Post by **DSguru2B** » Thu Feb 22, 2007 5:40 pm

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?

novneet · Post by **novneet** » Fri Feb 23, 2007 5:51 am

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....

AaronVG · Post by **AaronVG** » Fri Feb 23, 2007 1:07 pm

Let me see if I can clarify and answer the above three questions. And again, thanks!

chulett wrote:You don't have any kind of Julian date. Why is that particular error being reported?

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.

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?

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).

novneet wrote:Can you tell me what is the field format of the source and what is the field format of the target?

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).

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.

AaronVG · Post by **AaronVG** » Fri Feb 23, 2007 1:27 pm

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!

DSguru2B · Post by **DSguru2B** » Fri Feb 23, 2007 1:51 pm

That means you were not giving the format properly or something must be missing. By default its taking YYYY-MM-DD format. Nothing to be confused

One thing though, make sure the Juilan Dates got loaded properly.

DSXchange

String to Datestamp challenges

String to Datestamp challenges

Field format

I have gone insane