I have some flat file data that contains timestamps in the form of "YYYY/MM/DD HH:MI:SS AM/PM". I need to convert it to Oracle timestamp In the target Database.
Using a server job, I simple created a routine to do the transformation. However, the requirement is to have it done in a parallel job. What are some techniques to accomplish this?
Basically, I am thinking that I need to parse the last two chars and if they are PM, add 12 to the hour portion of the time. Then strip off the last two chars....Any suggestions on how to do in a parallel job?
TimeStamp From "yyyy/mm/dd hh:mi:ss AM/PM"
Moderators: chulett, rschirm, roy
TimeStamp From "yyyy/mm/dd hh:mi:ss AM/PM"
HAVE A GOOD ONE
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You are on the right track. You may need to trim the rightmost space characters after removing the AM/PM designator. You will need to do some conversion of the date components to perform the arithmetic.
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.
I created six(6) stage variables, probably could have gotten away with less to accomplish my task....
svCreatedOn := nulltovalue(lnSeqCode.CREATED_ON,"01/01/0001 12:00:00 AM")
svCrOnHr := trim(svCreatedOn[Index(svCreatedOn,":",1)-2,2] ," ","A")
svCrRestOfTime := svCreatedOn [Index(svCreatedOn,":",1),6]
svCrOnAMPM := Right(svCreatedOn,2)
svCrOnHr24 := if svCrOnAMPM = "PM" And svCrOnHr <12 then svCrOnHr + 12 else if svCrOnHr < 10 then "0":svCrOnHr else svCrOnHr
svCrOnTime := if index(svCreatedOn," ",1) > 0 then svCreatedOn [1,index(svCreatedOn," ",1)]: svCrOnHr24: svCrRestOfTime else svCreatedOn:" ":"01/01/0001"
This is ok, except for the fact that I have 5+ date fields that needto be transformed. There MUST be an easier way!
If I had a C compiler, I would create a routine, but I do not so I am stuck. Is it possible to create a function to do this? :D
svCreatedOn := nulltovalue(lnSeqCode.CREATED_ON,"01/01/0001 12:00:00 AM")
svCrOnHr := trim(svCreatedOn[Index(svCreatedOn,":",1)-2,2] ," ","A")
svCrRestOfTime := svCreatedOn [Index(svCreatedOn,":",1),6]
svCrOnAMPM := Right(svCreatedOn,2)
svCrOnHr24 := if svCrOnAMPM = "PM" And svCrOnHr <12 then svCrOnHr + 12 else if svCrOnHr < 10 then "0":svCrOnHr else svCrOnHr
svCrOnTime := if index(svCreatedOn," ",1) > 0 then svCreatedOn [1,index(svCreatedOn," ",1)]: svCrOnHr24: svCrRestOfTime else svCreatedOn:" ":"01/01/0001"
This is ok, except for the fact that I have 5+ date fields that needto be transformed. There MUST be an easier way!
If I had a C compiler, I would create a routine, but I do not so I am stuck. Is it possible to create a function to do this? :D
HAVE A GOOD ONE