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?