2 Replies Latest reply: Nov 26, 2012 9:21 PM by Martin Feckie RSS

    Problem parsing datetime

      Hi Folks,

       

      I'm having a real problem with date/time field.

       

      I need to be able to calculate the difference (in minutes) between two times.  When I load the data into Qlikview, it seems to be recognised only as text.  The datetimes come from a text file and are stored in the following format:

       

      1/2/2012 21:38:00
      1/3/2012 7:59:00
      1/3/2012 8:20:00
      1/3/2012 8:22:00

       

      If I look at the document properties -> tables tab, I can see that the field is tagged as $ascii, $text.

       

      I am able to use

       

      SubField(CHECKIN, ' ', 2) AS CHECKIN_TIME,
      SubField(CHECKIN, ' ', 1) AS CHECKIN_DATE
      

       

      This results in the field being recognised as time and date respectively.  I can then calculate the difference timewise.

       

      The problem is that some start finish combinations go past midnight and into the next day.  Subtracting without the date included results in negative figures.

       

      I've tried loading using Timestamp#(CHECKIN, 'D/M/YYYY hh:mm:ss'), but that doesn't seem to work either.

       

      I'm sure there's something I'm missing....