Oracle SQL Where Clause using date variable for filter

[#1]

Hi Experts,

I have following script to fetch and load data into incremental qvd and failing at date conversion.

LIB CONNECT TO 'oradb';

Set vQVDPath = QVDs/;

Set vThisExecTime=Now();

Let vLastExecTime = Timestamp(If(IsNull(QvdCreateTime('$(vQVDPath)EMP_ALLOC.QVD')), 0, ConvertToLocalTime(QvdCreateTime('$(vQVDPath)EMP_ALLOC.QVD'), 'GMT', 1)), 'YYYY-MM-DD hh:mm:ss');

EMP_ALLOC:

SELECT EMP_ID,SHIFT_DT,SHIFT_NO,SUPERVISOR_ID,LAST_UPDT

FROM EMP_ALLOC

WHERE LAST_UPDT>='$(vLastExecTime)'

AND LAST_UPDT<'$(vThisExecTime)';

[Ezir]

Sugiro fazer uma formatação da variável e do campo de busca para o mesmo formato de data conforme exemplo abaixo.

A variável ficaria assim:

Let vThisExecTime=Timestamp(Now(),'YYYY-MM-DD hh:mm:ss'); 

Let vLastExecTime = Timestamp(If(IsNull(QvdCreateTime('$(vQVDPath)EMP_ALLOC.QVD')), 0, ConvertToLocalTime(QvdCreateTime('$(vQVDPath)EMP_ALLOC.QVD'), 'GMT', 1)), 'YYYY-MM-DD hh:mm:ss');

E a claúsula WHERE ficaria assim:

WHERE to_char(LAST_UPDT,'YYYY-MM-DD hh24:mm:ss')>='$(vLastExecTime)'
AND to_char(LAST_UPDT,'YYYY-MM-DD hh24:mm:ss')<'$(vThisExecTime)';