timestamp_from_string with microseconds in Modify Stage

[rmcclure]

Hi,

I have a job that reads from an Oracle table and writes to a Dataset doing transformation on one field.
One of the fields (DATETIME_FIELD) has microseconds and it is stored in Oracle as a varchar
Example: 20161231 11:35:12.123456

I am trying to convert it to a TIMESTAMP with a Modify Stage using:
DATETIME_FIELD = timestamp_from_string[%yyyy%mm%dd %hh:%nn:%ss.%6] (DATETIME_FIELD)
but I get an error:
main_program: Error parsing modify adapter: Error in binding: Parsing parameters "%yyyy%mm%dd %hh:%nn:%ss.%6" for conversion "timestamp=timestamp_from_string[%yyyy-%mm-%dd %hh:%nn:%ss](string)": APT_Conversion_String_TimeStamp: Invalid format [%yyyy%mm%dd %hh:%nn:%ss.%6] used for string_from_time type conversion
Expected destination field selector, got: ")"; input:
DATETIME_FIELD = timestamp_from_string[%yyyy%mm%dd %hh:%nn:%ss.%6] (DATETIME_FIELD)

I also tried removing the % before the 6:
DATETIME_FIELD = timestamp_from_string[%yyyy%mm%dd %hh:%nn:%ss.6] (DATETIME_FIELD)
I do not get any errors but the data comes out as: 20161231 11:35:12.000000

Strangely enough if I use a Transformer Stage instead using the same formatting:
StringToTimestamp(DATETIME_FIELD,"%yyyy%mm%dd %hh:%nn:%ss.6")
The data is correct: 20161231 11:35:12.123456

In both cases the destination field on the Output tab is defined as Timestamp with Extended = Microseconds.

I can go with the transformer stage if need be but I would like to know why the modify is not working. Any ideas??
Thanks in advance.

[UCDI]

Not totally sure if this is remotely related but some stages, possibly due to age, do not seem to support microseconds. I ran into this on the basic-transformer. Could be the same issue in modify?

[Mike]

I can confirm that timestamp_from_string in a Modify stage in version 11.3 works exactly as expected with microseconds for me. Perhaps a version 8 bug?

Mike

[rmcclure]

Hi Mike,

We have a version of 11.5 in development where I tested this and I have the same problem. You mentioned that it works for you. What format are you using for the microseconds

[Mike]

I just whipped up a quick test job and am getting the expected microseconds.

Here is my modify spec:

Code: Select all

OUTPUT_VALUE = timestamp_from_string["%yyyy%mm%dd %hh:%nn:%ss.6"] (INPUT_VALUE)

My OUTPUT_VALUE is defined as a timestamp with the Microseconds extended property and my INPUT_VALUE is defined as an unbounded VarChar.

Any chance that your Oracle string is getting truncated before it hits the modify stage?

Mike

[rmcclure]

Hi Mike,

Thanks for the response.
The data is not getting truncated because my two tests are exactly the same except one uses modify and the other transformer.

[Mike]

I went back and reread your original post... and took note that your output was a parallel dataset.

In my quick test job, I just used a peek stage after the modify.

I substituted a dataset and the microseconds disappeared.

So I think we have a repeatable bug with modify output to a dataset.

As a potential workaround, you could try putting a copy stage between the modify and the dataset. I didn't try that, but that would be my first attempt at a workaround.

Mike

[rmcclure]

Hi Mike,

I tried the copy stage but get the same result.
A little fix to my original post: I said I wrote to a dataset but I was writing to a sequential file. That being said I did a number of tests with sequential file and Dataset.
-Writing to a sequential file turns the microseconds to .000000
-Writing to a Dataset removes the microseconds completely
-Using a Transformer results in correct data regardless of the destination.
-Strangely for me going from Modify to Peak also drops the microseconds.
-To test the output from a Modify stage I had a Transformer do the conversion to timestamp and hen passed it through the modify stage. The data was good.

For me it seemed a problem with the timestamp_from_string function in the modify stage but then why different results based on output destination? It is still a mystery.