DSXchange

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.

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?

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

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

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

Here is my modify spec: 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

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.

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

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.