
Not applicable
2017-03-02
01:37 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Oracle SQL Where Clause using date variable for filter
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)';
1,755 Views
1 Reply

Creator II
2019-05-13
02:50 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)';
1,327 Views
