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.