Not a valid month error (ORA-1843)
628959Mar 17 2008 — edited Mar 8 2010I am trying to query table using start_date and end_dates. I think to_date() function is giving some problem and I am getting an exception "ORA-1843: not a valid month". Here is the procedure below.
------------------------------------------------------------------------------------------------------
PROCEDURE USP_SEARCH_REQUESTS(P_START_EVENT_DATE IN DATE,
P_END_EVENT_DATE IN DATE,P_REF_CURSOR OUT REF_CURSORT_TYPE) IS
sql_stmt varchar2(4000) := null;
BEGIN
sql_stmt := 'SELECT * FROM REQUEST WHERE REQUEST_ID >0';
IF ((P_START_EVENT_DATE IS NOT NULL) AND (P_END_EVENT_DATE IS NOT NULL)) THEN
sql_stmt := sql_stmt || ' AND EVENT_DATE BETWEEN to_date(''' || P_START_EVENT_DATE || ''', ''mm/dd/yyyy'') AND to_date(''' || P_END_EVENT_DATE ||''', ''mm/dd/yyyy'')';
ELSIF ((P_START_EVENT_DATE IS NULL) AND (P_END_EVENT_DATE IS NOT NULL)) THEN
sql_stmt := sql_stmt || ' AND EVENT_DATE BETWEEN to_date(''01/01/1900''' || ', ''mm/dd/yyyy'') AND to_date(''' || P_END_EVENT_DATE ||''', ''mm/dd/yyyy'')';
ELSIF ((P_START_EVENT_DATE IS NOT NULL) AND (P_END_EVENT_DATE IS NULL)) THEN
sql_stmt := sql_stmt || ' AND EVENT_DATE BETWEEN to_date(''' || P_START_EVENT_DATE || ''', ''mm/dd/yyyy'') AND to_date(''12/31/9999''' ||''', ''mm/dd/yyyy'')';
OPEN P_REF_CURSOR for sql_stmt;
END IF;
END;
-------------------------------------------------------------------------------------------------------
I am calling this procedure from C# dotnet code. any problem with this code.
please help.
thanks
RJ