Help converting to timestamp

[Anonymous]

Hello,

I have a timestamp field that looks like this:

It is being read as text and I've tried using Timestamp# to convert it, but it is still tagged as $ascii $text:

    Timestamp#(Replace([timestamp],' GMT ',' '),'WWW MMM DD hh:mm:ss YYYY') as newTS,

  

Would really appreciate some help with this!

Thanks,

Tanya

[Anonymous]

I am on Qlik Sense 2.2.3

[sunny_talwar · Accepted solution]

May be try this

Timestamp#(Mid(Replace([timestamp],' GMT ',' '), Index(Replace([timestamp],' GMT ',' '), ' ')+1),'MMM DD hh:mm:ss YYYY') as newTS

[MK_QSL]

Try this

Dual(TimeStamp#(Replace(timestamp,' GMT',''),'MMM DD hh:mm:ss YYYY'),TimeStamp#(Mid(Replace(timestamp,' GMT',''),5),'MMM DD hh:mm:ss YYYY')) as NewTS

[Anonymous]

That works! Can you explain why?

[sunny_talwar]

For some reason, the TimeStamp#() or Date#() are unable to read the WeekDay using WWW. I just removed it from the interpretation to fix this

[Anonymous]

If you  want to convert this to other Format Like,

06/07/2017 10:30:40

you can use below expression:

Replace Time with your fieldname.

Timestamp#(DATE(DATE#(LEFT(RIGHT(replace(Time, 'GMT',''),21),6)&' '&RIGHT(RIGHT(replace(Time, 'GMT',''),21),4),'MMM DD YYYY'),'MM/DD/YYYY')&' '&SubField(Time,' ',4),'MM/DD/YYYY HH:MM:SS') as #Timestamp

[Anonymous]

Thanks Sunny!