TimeStamp From "yyyy/mm/dd hh:mi:ss AM/PM"

[zam62]

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?

[ray.wurlod]

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.

[zam62]

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