Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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;

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?

4 Replies
Contributor III

Re: Timestamp issue in incremental load


There is a little problem with your script. You load the field LASTMODIFIEDDATE from the QVD but you try to read the field LastModifiedDate to save in the variable. Change the line

LET vLastExecTime = peek('LastModifiedDate',0,'LoadTime');


LET vLastExecTime = peek('LASTMODIFIEDDATE',0,'LoadTime');

and see if it solves the problem.


Not applicable

Re: Timestamp issue in incremental load

Thank you Bruno,

Now I have to somehow fix the format. I think I am just not capable to create timestamp valid in SQL

this is the issue now:


ErrorSource: OraOLEDB, ErrorMsg: ORA-01843: not a valid month


where 1=1

and rank =1

and LASTMODIFIEDDATE >= '26.06.2014 23:45:14'

and LASTMODIFIEDDATE < '28.06.2014 11:01:04'

Thank you

Contributor III

Re: Timestamp issue in incremental load

Hi, Matej.

So we have to figureout the timestamp format of your database. Let's try setting the variables like this:

LET vExecTime = Timestamp(LocalTime(), 'MM-DD-YYYY hh:mm:ss');

LET vLastExecTime = Timestamp(Peek('LASTMODIFIEDDATE'), 'MM-DD-YYYY hh:mm:ss');

If it doesn't work, try changing the 'MM-DD-YYYY hh:mm:ss' until it matches the DB timestamp format.

Re: Timestamp issue in incremental load

Oracle Timestamp literal format is


Note the string "TIMESTAMP" is part of the specification, that's not a QV function.

If you use Qlikview Components (QVC) to do your incremental loads, QVC has knowledge of the formats used by various databases.



Community Browser