How to make a timestamp out of two fields?

[#1]

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

[#2]

Hi, could you post an example of your data?

[johnw · Accepted solution]

How about this?

timestamp(date#(End_date,'YYYY-MM-DD') + time#(num(End_time,'0000'),'hhmm'),'YYYY-MM-DD hh:mm') as End_timestamp

See attached example.

[#4]

Here is another expression sample where the input time can be from null to n digits:

let startdate='2009-10-29';
let intime=108;
let starttime=maketime(
if(mid(intime,1,2)<1,0,mid(intime,1,2)),
if(mid(intime,3,2)<1,0,mid(intime,3,2)),
if(mid(intime,5,2)<1,0,mid(intime,5,2))
);
let combo=startdate&' '&starttime;
let timestamp=timestamp#(combo,'YYYY-MM-DD HH:MM:SS');

--Arun

[#5]

Thank you all for your help!

Mr Witherspoon's suggestion worked fine and now it's all up and running.

Mi