4 Replies Latest reply: Jun 28, 2014 5:59 PM by Rob Wunderlich RSS

    Timestamp issue in incremental load


      Hi Guys,

       

      This is my first question that I was not able to solve by reading your answers.

       

      Within incremental load logic there is condition within SQL statement. The condition is as folowing:

       

      where LASTMODIFIEDDATE >= '$(vLastExecTime)' and LASTMODIFIEDDATE < '$(vExecTime)'

       

      while variables are filled as follows:

      LET vExecTime = LocalTime();

      IF (NOT isNull(QvdCreateTime('$(vQVDPath)M_LOANREQUEST.qvd'))) THEN
      LoadTime:
      LOAD Max(LASTMODIFIEDDATE) as LASTMODIFIEDDATE
      FROM $(vQVDPath)M_LOANREQUEST.qvd (qvd);
      LET vLastExecTime = peek('LastModifiedDate',0,'LoadTime');
      DROP Table LoadTime;
      END IF;

       

      the issue is that I receive the latest date - end of day yesterday. So I suppose there is some missmatch in hour/minute/seconds formats in SQL and Qlikview.

      this is format of date in QV

      SET TimestampFormat='DD.MM.YYYY h:mm:ss[.fff]';

      this is timestamp format in SQL developer

      DD-MON-RR HH.MI.SSXFF AM

       

      I have tried several options such as:

       

      and LASTMODIFIEDDATE >= to_timestamp('$(vLastExecTime)','DD-MON-RR HH.MI.SSXFF AM TZR')

       

      however I still receive just end of day yesterday. The source is updated online and this is very much needed.

       

      Can you please help me how I can compare timestamps in SQL statements using variables?