Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Author

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
Author

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
Author

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
Author

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

Anonymous
Not applicable
Author

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

Where  Modification Time >= '$(LastExecTime)'

Not applicable
Author

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
Author

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!