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

[Qlik_Enthu]

[prma7799]

I am not getting clear idea.

Just do one thing execute below query

SELECT CUSORD_DATE  FROM COS.CUSTOMER_ORDER;

And within second terminate above query and share output screenshot with us .

Like below

[Qlik_Enthu]

PM, checked in ORacle DB and found the data type for the field is "MM/DD/YYYY" however, while trying to load into QV, the create select statement dialog box shows as given below (SQL timestamp) No idea where and how I can modify the date format as we do while uploading xcel files. I am so confused

[prma7799]

I am also confusing

Try like this

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

[Kushal_Chawda]

try this

LET vToday= date(Today(1),'MM/DD/YYYY');

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 to_date(to_char(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= to_date('$(vToday)','MM/DD/YYYY');

[Qlik_Enthu]

It WORKED!!!!! Thanks much!!

THank you everyone for taking efforts !!!

[Kushal_Chawda]

Please mark it as correct answer, so that it will help others to look for similar problem

[Qlik_Enthu]

THIS I already tried and did not worked out

[Qlik_Enthu]

this is my code that worked:

LET vToday=Date(Today(1),'MM/DD/YYYY');
ODBC CONNECT TO [PT1025;DBQ=PT1025.IKEADT.COM] (XUserId is afMJeBdJTSdA, XPassword is ZBJfUBdJTaNOTbYW);
//-------- Start Multiple Select Statements ------
LOAD "CUSORD_NO",
"CUSORD_STAT",
"CUSORD_DATE",
"CUST_NO",
"CUS_NAME",
"CTY_CODE",
"USER_ID_REF";
SQL SELECT CUSORD_NO,CUSORD_STAT,CUSORD_DATE,CUST_NO,CUS_NAME,CTY_CODE,USER_ID_REF
FROM COS."CUSTOMER_ORDER" WHERE CUSORD_STAT='00' AND TO_DATE(TO_CHAR(CUSORD_DATE,'MM/DD/YYYY'),'MM/DD/YYYY')>= TO_DATE('$(vToday)','MM/DD/YYYY');
//CUSORD_DATE >= $(vToday);
//-------- End Multiple Select Statements ------

[Kushal_Chawda]

glad that it was helpful