Oracle Query - Invalid Date Error - PLease Help
Hi...
I want to develop a dynamic search query in toad (oracle 9i).
My Query is -
PROCEDURE MY_QUERY
(
IN_OPTION NUMBER,
IN_FROM DATE,
IN_TO DATE,
IN_COL1 NUMBER,
IN_COL2NUMBER,
IN_COL3 NUMBER,
OUT_CUR OUT RPT_CUR
)
AS
MY_QUERY VARCHAR2(4000);
MY_WHERE_CLAUSE VARCHAR2(4000) ;
IF IN_OPTION IS NOT NULL AND IN_OPTION= 1 THEN
MY_WHERE_CLAUSE := MY_WHERE_CLAUSE || 'AND DATE_COL1 BETWEEN' || TO_DATE(IN_FROM,'dd-MM-yyyy') || 'AND' || TO_DATE(IN_DATE_TO,'dd-MM-yyyy');
END IF;
IF IN_OPTION IS NOT NULL AND IN_OPTION= 2 THEN
MY_WHERE_CLAUSE := MY_WHERE_CLAUSE || 'AND DATE_COL2 BETWEEN' || TO_DATE(IN_FROM,'dd-MM-yyyy') || 'AND' || TO_DATE(IN_DATE_TO,'dd-MM-yyyy');
END IF;
IF IN_COL1 IS NOT NULL THEN
MY_WHERE_CLAUSE := MY_WHERE_CLAUSE || 'AND COL5 = ' || IN_COL1;
END IF;
MY_QUERY := MY_QUERY || ' SELECT vwo.COL3, vwo.COL4, ';
MY_QUERY := MY_QUERY || ' (SELECT cm.COL2 from TABLE_MASTER_1 cm where cm.DELETED_STATUS = 0 and cm.TID = vwo.COMPANY_ID )as Company ';
MY_QUERY := MY_QUERY || ' from TABLE_12 vwo where
vwo.COL_17 IN ';
MY_QUERY := MY_QUERY || ' ( ';
MY_QUERY := MY_QUERY || ' SELECT vcomp.COL_17 from TABL_17 vcomp where vcomp.DELETED_STATUS = 0 ';
MY_QUERY := MY_QUERY || MY_WHERE_CLAUSE ;
MY_QUERY := MY_QUERY || ' ) ';
How do I do this ??
Values in Date Variables -
Date_From (DateTime) - 01-01-2015
Date_To (DateTime) - 28-02-2015
ERROR - INVALID MONTH AT LINE 12.
PL/SQL ERROR.
-------
I also tried doing it withe VARCHAR Datatype and using TO_CHAR(Date_From,'dd-MM-yyyy') function. But this also did not work.
PLease help