Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Date Check During Incremental Load

Hi Experts,

I am working on incremental load but i am facing a issue during the date check to load the fresh records.When i put the condition

"Where  Modification Time >= LastExecTime"

I am able to capture the lastExecTime from the alredy loaded qvd and it is comming in MM-DD-YYYY format. But i am not able to match the above condition because of some format issue with the two dates.

Can you suggest some functions or some other solution which i should use with the field having the Modification time.I have already used Date function with MM-DD-YYYY format but it does not work.

Thanks in advance for your help

7 Replies
Not applicable

Re: Date Check During Incremental Load

Hi Ronny, The variable holds the value in string format. So you need to convert the variable value into desired date field format.

If DB is oracle:

"Where  Modification Time >= TO_DATE('$(LastExecTime)','MM-DD-YYYY')"

Not applicable

Re: Date Check During Incremental Load

Hi Dathu

Thanks for your response.My DB is SQL, can you suggest some function which can be used with the variable  when working with SQL DB.

Thanks

Not applicable

Re: Date Check During Incremental Load

Hi Ronny,

Can you try like below:

"Where  Modification Time >= CONVERT(VARCHAR(10), '$(LastExecTime)', 110)"

I assumed your database is MS SQL Server.

Thanks,

Angad

Not applicable

Re: Date Check During Incremental Load

For SQL Server you can use chr(39) to surround your value:

Let vLoadTime = chr(39) & Date(Now(),'M/D/YYYY h:mm:ss[.fff] TT') & chr(39);

and then simply refer to the variables:

 

where lst_mod_ts >= $(vLastLoadTime)
and lst_mod_ts <
$(vLoadTime);

mov
Esteemed Contributor III

Re: Date Check During Incremental Load

It is simpler in MS SQL.  This always works for me:

Where  Modification Time >= '$(LastExecTime)'

Not applicable

Re: Date Check During Incremental Load

If your DB is Oracle you can use TO_DATE or TO_TIMESTAMP functions to convert the strings into date formats.

If your DB SQL Server you dont need any function.

Not applicable

Re: Date Check During Incremental Load

Michael Solomovich and dathu.qv are right: MS SQL Server generally interprets the expanded value correctly as a date without the need for any functions. However, you DO need the single quotes around it!

Community Browser