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);

[prma7799]

Try like this

Date(Date#(CUSORD_DATE,'mm/dd/yyyy'),'MM/DD/YYYY') as NewDate

[Qlik_Enthu]

This has to be added in SQL part or in Load?

Thank you!

[prma7799]

Can you share sample output ...

Or you can share format for the dates

[Qlik_Enthu]

hi below is my data preview loaded from the table:

[Qlik_Enthu]

and I have  variable which stores the value of today(1) in mm/dd/yyyy format. this has to be compared with the cusord_date value

[prma7799]

Try like this

date((Timestamp(CUSORD_DATE) ),'DD-MM-YYYY')

[Qlik_Enthu]

Not working!

Error: SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "ORDER_DATE": invalid identifier

My Load and SQL code:

"CUST_ORDER_EU":
LOAD USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
CUSORD_DATE,
Date((Timestamp(CUSORD_DATE)),'MM/DD/YYYY') As ORDER_DATE,
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 "ORDER_DATE" >=$(vToday);

[Anil_Babu_Samineni]

Would you able to provide sample data

[prma7799]

first try to  load only table and convert it without any changes into qvd then change date format

Like this

NewTable:
LOAD USER_ID_REF,
CUSORD_NO,
CUSORD_STAT,
CUSORD_DATE,
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 "ORDER_DATE" >=$(vToday);