topic Re: Need help on this date format in QlikView

Need help on this date format

NZFei — Mon, 26 Jan 2026 18:19:17 GMT

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

Re: Need help on this date format

Gysbert_Wassenaar — Mon, 13 Feb 2017 05:38:56 GMT

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)

Re: Need help on this date format

NZFei — Mon, 13 Feb 2017 19:30:22 GMT

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

Re: Need help on this date format

NZFei — Mon, 13 Feb 2017 19:35:57 GMT

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')