Extract Time from Date and Time field

[davehutchinson]

Hi, I'm going round and round with this one.  i've tried various solutions that have already been posted but none of them are working.

I have a field in my data called "StartDateTime" which is the very annoying format of YYY-MM-DD HH:MM:SS and then a 3 letter code at the end.

The three letter code is causing the issues I think, and I don't know how to tell Qlik to ignore it.

I've been able to extract the date on its own by doing the following:

Date(Date#(left(Date_Time_Session_Start,10),'YYYY-MM-DD'),'DD/MM/YYYY') as StartDate,

However I can't extract the time on its own.

I have tried the following but none work:

Time(Frac(Right([Date_Time_Session_Start],5)),'hh:mm') as "Start Time",
Time(TimeStamp#([Date_Time_Session_Start],'DD/MM/YYYY HH:MM'),'HH:MM') as StartTime,

Can anyone tell me how to extract the time on its own??

Thanks!!

[davehutchinson · Accepted solution]

Ah ha! I have cracked it!!

I used:

Mid(Date_Time_Session_Start,12,5) as Start_Time,

So basically I extracted the 5 characters needed from the middle of the string.

I'm sure there's a better way of doing it, but that one did the trick.