Date conversion and comparison in Where clause not successfull

[Qlik_Enthu]

Dear All,

Below is my code. I have tried all the possible combos of date conversion functions to compare today`s date (mm/dd/yyyy) with a date field from the data source., but it is just not working. The date field is in ts format and I am finding it difficult to convert to mm/dd/yyyy format. ITs quite urgent and help on this is very much appreciated.

LET vToday=Today(1);
ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is RQNcTBdJTSaA, XPassword is efNSaBdJTaNOTbAN);
ODBC CONNECT TO [PT1028;DBQ=PT1028.IKEADT.COM] (XUserId is PTdbUBdJTaFB, XPassword is cIGOeBdJTaNOTbYU);
QUALIFY *;
UNQUALIFY CUSORD_NO;
//-------- Start Multiple Select Statements ------
"CUST_ORDER_EU":
LOAD USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
Date(CUSORD_DATE,'MM/DD/YYYY'),
CUST_NO,
CUS_NAME,
CTY_CODE;
SQL SELECT
USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
Convert(CUSORD_DATE,101),
CUST_NO,
CUS_NAME,
CTY_CODE
FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' AND "CUSORD_DATE" >=$(vToday);

[jonathandienst]

ORDER_DATE does not exist in the SQL source, and there is some confusion regarding date formats and Date() <format> and Date#() <interpret> functions. You say MM/DD/YYYY, but your sample above looks like M/D/YYYY. This code will fix those issues:

Let vToday = Date(Today(), 'M/D/YYYY');   // or MM/DD/YYYY

CUST_ORDER_EU:

LOAD USER_ID_REF,

  CUSORD_NO,

  CUSORD_STAT,

  CUSORD_DATE,

  Date#(SubField(CUSORD_DATE, ' ', 1), 'M/D/YYYY') As ORDER_DATE, // strip time and interpret

  CUST_NO,

  CUS_NAME,

  CTY_CODE;

SQL SELECT

  USER_ID_REF,

  CUSORD_NO,

  CUSORD_STAT,

  CUSORD_DATE,

  CUST_NO,

  CUS_NAME,

  CTY_CODE

FROM COS.CUSTOMER_ORDER WHERE CUSORD_STAT ='00' AND CUSORD_DATE >= '$(vToday)';

[Qlik_Enthu]

Anil, I am loading this data from DB and not sure I can pull out the sample data bcoz there are millions of records.

[Qlik_Enthu]

is there anything wrong with my script?

[Qlik_Enthu]

Jonathan, tried the above and getting the following error:

SQL##f - SqlState: S1000, ErrorCode: 932, ErrorMsg: [Oracle][ODBC][Ora]ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL SELECT
USER_ID_REF,
    CUSORD_NO,
    CUSORD_STAT,
    CUSORD_DATE,
    CUST_NO,
    CUS_NAME,
    CTY_CODE
FROM COS.CUSTOMER_ORDER WHERE "CUSORD_STAT" ='00' AND "CUSORD_DATE" >=06/15/2017

[prma7799]

Try like this

"CUSORD_DATE" > = '06/15/2017';

[Qlik_Enthu]

PM actually  the date variable holding today`s date works fine..the problem is with the sql conversion only. However tried this  and no luck! says "not a valid month"

[prma7799]

Please check your date format in database.

[prma7799]

I am not sure but  try like this ...

"CUSORD_DATE" >=  SYSDATE

[Qlik_Enthu]

I have initially tried this. This will execute but wont fetch any records bcoz SYSDATE resides in DUal table. In my DB Dual table is dummy and doesn't have any fields nor data.

[prma7799]

Try to check Description of table

Describe COS.CUSTOMER_ORDER;