Modify - String_To_Timestamp

[Raftsman]

I used the following syntax in the modify stage

timestamp_col:timestamp = timestamp_from_string [%yyyy-%mm-%dd hh:nn:ss] (string_col)

Right from the manual.

Source is a string parameter field pmTIMESTAMP = 2008-01-31 12:00:00
Target is : STAGING_LOAD_DATE datatype: timestamp

Syntax is : STAGING_LOAD_DATE:timestamp=timestamp_from_string [%yyyy-%mm-%dd hh:nn:ss] (#pmTIMESTAMP#)

I get the following error
main_program: Error parsing modify adapter: Error in binding: Expected source field selector; got: "2008"
Expected destination field selector, got: ")"; input:
STAGING_LOAD_DATE=timestamp_from_string (2008-01-31 12:00:00)
;

Can someone please help and explain what is wrong. I tried a few changes by removing the defaults, add quotes etc. Still can't get it to work.

Thanks

[ray.wurlod]

Can you try it naming an input column explicitly rather than using a job parameter reference? I have some doubt as to whether Modify stage can use job parameters in this context.

If that works, insert an upstream Column Generator stage to load the job parameter value into a column.

[Raftsman]

Hi Ray,

I added a column generator but I am not sure how to get the parameter as the initial value. I tried Epoch but it keeps adding one minute to the time extension of the timestamp. If I can get this to work, I do not need the Modify stage.

Is this what you were referring too.\

Thanks

[ray.wurlod]

What I had in mind was a Cycle with only one value, and that a job parameter (but I'm not 100% sure you can do that). But you could certainly generate a constant Char(1) value of "X" and join that to a single row virtual Data Set keyed by "X" and containing the job parameter value, probably created with a Row Generator stage.

[Raftsman]

it seems like alot of work in place of using the transformer stage. I realize there are optimization issues with the transformer but my volumes are low. Nothing over 600,000 rows. all I am trying to do is add a load date to the staging area. By using a virtual dataset introduces another piece of the puzzle.

[ray.wurlod]

There is a virtual Data Set associated with every link in your design; you'd not be introducing one.

At version 7.5.2 (maybe even 7.5.1) the Transformer stage has been heavily re-worked - it basically generates an operator these days, and most of the warnings about preferring not to use it belong in the past.

[manishk]

Syntax is : STAGING_LOAD_DATE:timestamp=timestamp_from_string (column_name)

This will bring it to the format [%yyyy-%mm-%dd hh:nn:ss]

Dont put the job parameter here. assisgn the parameter to "column_name" in seprate stage or use the row_generator or column generator


This should work

[ray.wurlod]

Don't you need percent signs on the time components in the format string also?

Code: Select all

%yyyy-%mm-%dd %hh:%nn:%ss
              ^   ^   ^

[manishk]

There is no Need of "%". Ny default it will put that in this format.

Don't you need percent signs on the time components in the format string also?

Code: Select all

%yyyy-%mm-%dd %hh:%nn:%ss
              ^   ^   ^

...