0 Replies Latest reply: Oct 26, 2009 5:17 PM by adbmita RSS

    How to make a timestamp out of two fields?

      Dear all,

      I've been trying to solve this time issue for days now and hope someone could help me out.

      What I want to know is the processing time for an activity to be performed. I got date fields with the format 'YYYY-MM-DD' and time fields with the format 'NNNN'. e.g '1' is 00:01 AM, and '213' the same as 2:13 AM. What I want is to have both the date and the time fields combinded so I get the format 'YYYY-MM-DD hh:mm' so I can count how long time in hours the processing time is.

      I thought I could use the timestamp()-function but maybe I'm missing out on something or using the wrong function to solve this.

       

       



      ActivityProcess:
      Load *,
      //Makes a time format out of the time field
      if(len(Start_time)<3,'00:'&time(time#(Start_time,'mm'),'mm'),
      if(len(Start_time)<4 and len(Start_time)>2, time(time#(left(Start_time,1),'hh'), 'hh')&':'&time(time#(right(v,2),'mm'),'mm'),
      if(len(Start_time)<5 and len(Start_time)>2, time(time#(left(Start_time,2),'hh'), 'hh')&':'&time(time#(right(Start_time,2),'mm'),'mm')))) as Start_time,
      if(len(End_time)<3,'00:'&time(time#(End_time,'mm'),'mm'),
      if(len(End_time)<4 and len(End_time)>2, time(time#(left(End_time,1),'hh'), 'hh')&':'&time(time#(right(v,2),'mm'),'mm'),
      if(len(End_time)<5 and len(End_time)>2, time(time#(left(End_time,2),'hh'), 'hh')&':'&time(time#(right(End_time,2),'mm'),'mm')))) as End_time,
      timestamp(timestamp#(End_date&End_time, 'YYYY-MM-DD hh:mm')-timestamp#(Start_date&Start_time, 'YYYY-MM-DD hh:mm'), 'YYYY-MM-DD hh:mm') as ProcessTime;
      Load
      Activity_ID,
      Start_date,
      Start_time,
      End_date,
      End_time
      From ..

      If I don't get the date and the time in the format 'YYYY-MM-DD hh:mm' I don't know how to do the interval count in hours.

      Kind regards
      Mi