Modify - String_To_Timestamp
Moderators: chulett, rschirm, roy
Modify - String_To_Timestamp
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
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
Jim Stewart
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
If that works, insert an upstream Column Generator stage to load the job parameter value into a column.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
Jim Stewart
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
Jim Stewart
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Don't you need percent signs on the time components in the format string also?
Code: Select all
%yyyy-%mm-%dd %hh:%nn:%ss
^ ^ ^
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
There is no Need of "%". Ny default it will put that in this format.
ray.wurlod wrote:Don't you need percent signs on the time components in the format string also?...Code: Select all
%yyyy-%mm-%dd %hh:%nn:%ss ^ ^ ^
Thanks
Manish
Manish