Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Not a valid month error (ORA-1843)

628959Mar 17 2008 — edited Mar 8 2010
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 5 2010
Added on Mar 17 2008
7 comments
17,288 views