Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')"
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
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
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);
It is simpler in MS SQL. This always works for me:
Where Modification Time >= '$(LastExecTime)'
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.
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!