Need help on this date format

[NZFei]

Hi all,

I load some data from some web API with this date format. Can you anyone know how to translate it into normal date format in QlikView / Sense?

Thank you very much!

Fei

[Gysbert_Wassenaar]

That looks like a unix timestamp embedded in a text string. Something like this might do the trick:

Timestamp( MakeDate(1970) + Num(TextBetween(Date, '(','+')) /60/60/24)

[NZFei]

Hi Gysbert,

Thank you for your reply. If we take /Date(1479078000000+0100)/ as an example, it returns 12/02/48840 8:00:00 AM. How to get the date (48840) please?

Cheers.

Fei

[NZFei · Accepted solution]

I think I have figured out. Some other article says we need to divide it by 1000 first so I have tried this and it looks working.

=Timestamp( MakeDate(1970) + Num(TextBetween(Date, '(','+'))/1000/60/60/24)

Thank you very much Gysbert!

Fei

Another thing: the result is UTC so to change to local time we need to use converttolocaltime function, for example when converting it to New Zealand time, it will be like ths:

=ConvertToLocalTime(Timestamp( MakeDate(1970) + Num(1487014261)/1000/60/60/24),'Wellington')