Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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