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



      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?