3 Replies Latest reply: Feb 13, 2017 4:46 PM by Fei Xu RSS

    Need help on this date format

    Fei Xu

      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

          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
              Fei Xu

              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
                  Fei Xu

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