topic ODBC Oracle Datetime problem in QlikView

ODBC Oracle Datetime problem

Tue, 21 May 2013 09:42:12 GMT

Dear All,

I am new to qlikview,

I try to retrieve record from DB with SQL statement Below.

statement:

select * from itemdm where lastmodtime > TO_DATE($(vMax),'YYYY-MM-DD HH24.MI.SS');

debugger returns:

SQL##f - SqlState: S1000, ErrorCode: 907, ErrorMsg: [Oracle][ODBC][Ora]ORA-00907: missing right parenthesis

New:

select * from xf_itemdm where xf_lastmodtime > TO_DATE(2013-05-20 17.05.41,'YYYY-MM-DD HH24.MI.SS')

I guess this msg is due to missing pair of ' ,

can anyone teach me how to handle it?

and I found it there's a date but no datetime function,
Any ways suggested to handle the date, string and datetime conversion?

Thx !!!

Sokkorn — Tue, 21 May 2013 09:56:56 GMT

Hi calvinlin,

First try this: SQL select * from itemdm where lastmodtime > TO_DATE($(vMax),'YYYY-MM-DD HH24.MI.SS');

If not work then try select * from itemdm where lastmodtime > Chr(39) & TO_DATE($(vMax),'YYYY-MM-DD HH24.MI.SS') & Chr(39);

Regards,

Sokkorn

Tue, 21 May 2013 10:11:34 GMT

Dear Sokkorn,

Chr(39) works if i store the variable before the sql statement like this:

let vMax = chr(39)&DATE(peek('maxDate',-1,Temp),'YYYY-MM-DD HH.MM.SS')&chr(39);

when undergoing the sql statement, the vMax contains the pairs of 's

Thanks a lot !

Calvin Lin

Sokkorn — Tue, 21 May 2013 10:16:25 GMT

Dear Calvin,

Sound great. My script is just an idea for your to us Chr(39), but you can get it done with your actual script.

Cheer!!!

Sokkorn