No data using date in oracle DB where clause

[monaali602]

Hi all,

I am using ODBC Connection to Oracle DB and need to apply incremental load using date

I have written the following code

MaxDateTable:

LOAD Max(CREATION_DATE) AS MaxDate FROM [filename.qvd](qvd);

LET vMaxCrtDate = Date(Peek('MaxDate',0,'MaxDateTable'),'DD/MM/YYYY hh:mm:ss');

[Updatedtable]:

LOAD

fld1,

fld2;

SQL

SELECT fld1,fld2

FROM tablename

where CREATION_DATE = TO_DATE('$(vMaxCrtDate)', 'DD/MM/YYYY HH24:MI:SS');

The equals operator in where clause does not retrieve any data

in addition when replace where clause with

TRUNC(CREATION_DATE) = '29/04/2011'

no data retrieved despite same query run on TOAD and 43 records retrieved.

any help? thanks in advance

[zhadrakas]

you could try to compare date as numbers:

QV:

LET vMaxCrtDate = num(Peek('MaxDate',0,'MaxDateTable'));

Oracle:

where  ceil (sysdate - to_date(CREATION_DATE, 'DD/MM/YYYY HH24:MI:SS')) = $(vMaxCrtDate);

regards

tim

[monaali602]

Thanks for help

it doesn't work also

but when i used floor(creation_date) = '29/04/2011' in load where statement instead of SQL it retrieved data correctly,

the problem is in SQL is there special interpretation for date in ODBC i tried many formats.

[sth_wch004]

SQL:

SELECT a.fld1,a.fld2

FROM tablename a

where TRUNC(a.CREATION_DATE) = (SELECT TRUNC(MAX(b.CREATION_DATE)) FROM  tablename b)