Riddhi Valecha

Riddhi Valecha

  • 443
  • 3.3k
  • 410.5k

Oracle Query - Invalid Date Error - PLease Help

Feb 17 2015 7:43 AM
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

Answers (2)