STRING TO INVALID TIMESTAMP CONVERSION
Moderators: chulett, rschirm, roy
STRING TO INVALID TIMESTAMP CONVERSION
Hi All,
My job design is like this
SEQFILE--------TFM--------MLOADstage.
In this job i have a one column in file which is containing timestamp value
in the format 09020517090634.(YYYY-MM-DD:HH:MM:SS)
But few rows are coming with 09020517090668(YYYY-MM-DD:HH:MM:SS);
Rows containing this value 09020517090668 is populating in the target table as null value because of number of seconds is exceeding more than 60.
Now i want to populate this value 09020517090668 as 09020517090658 in the target table .how can i convert this rows?
Please help me if any body knows.
My job design is like this
SEQFILE--------TFM--------MLOADstage.
In this job i have a one column in file which is containing timestamp value
in the format 09020517090634.(YYYY-MM-DD:HH:MM:SS)
But few rows are coming with 09020517090668(YYYY-MM-DD:HH:MM:SS);
Rows containing this value 09020517090668 is populating in the target table as null value because of number of seconds is exceeding more than 60.
Now i want to populate this value 09020517090668 as 09020517090658 in the target table .how can i convert this rows?
Please help me if any body knows.
sa
First off, the format is not "YYYY-MM-DD:HH:MM:SS" as there are no separators in your data. Second, you'd need to spell out your requirements in words, one example is not enough to go on. What is the business rule that needs to be enforced when the seconds are > 60?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
HI Chulett,
My column format in target table is YYYYMMDDHHMISS.
i need seperators like YYYY-MM-DD HH:MM:SS.
For this already i have concatenated using oconv funtions in transformer stage.
Now my problem is
few records coming from source file for this column is containing seconds >60.
My business rule is i need to populate the data with seconds <60
If the records are coming with seconds >60 .
My column format in target table is YYYYMMDDHHMISS.
i need seperators like YYYY-MM-DD HH:MM:SS.
For this already i have concatenated using oconv funtions in transformer stage.
Now my problem is
few records coming from source file for this column is containing seconds >60.
My business rule is i need to populate the data with seconds <60
If the records are coming with seconds >60 .
sa
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Use a format mask.
Reject records with the rightmost two characters >= "60". A constraint expression on the "good" output could be:
Code: Select all
Fmt(InLink.TheString,"L####-##-## ##:##:##")
Code: Select all
Right(InLink.TheString,2) < 60
Last edited by ray.wurlod on Wed Apr 29, 2009 12:44 am, edited 1 time in total.
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.
Sorry but that doesn't explain anything. How do you reduce the seconds to less than 60? What is the transformation business rule?sankar.td wrote:few records coming from source file for this column is containing seconds >60.
My business rule is i need to populate the data with seconds <60
If the records are coming with seconds >60 .
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi Chulet,
I have timestamp column in target table.For that column few records coming from source are like this
09020517090667
09020517090668
0902-05-17 09 :06 :68
(YYYY-MM-DD HH:MM:SS)
These records are populating as NULL values in my target table.Because seconds >60.
Now my business rule is
I want to populate the records having seconds >60 (0902-05-17 09 :06 :68)
as seconds<60 (0902-05-17 09 :06 :58)
That is my requirement?
I have timestamp column in target table.For that column few records coming from source are like this
09020517090667
09020517090668
0902-05-17 09 :06 :68
(YYYY-MM-DD HH:MM:SS)
These records are populating as NULL values in my target table.Because seconds >60.
Now my business rule is
I want to populate the records having seconds >60 (0902-05-17 09 :06 :68)
as seconds<60 (0902-05-17 09 :06 :58)
That is my requirement?
sa
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: