Have a talk with your developers.
Implicit conversions of any kind should not be allowed in production code. I think you know why, now. Jared On Saturday 05 January 2002 04:35, Oracle DBA wrote: > we have set nls_date_format to dd-mm-yyyy in parameter file . > > but all the funtions which have some hard coded date value in the format of > dd-mm-yyyy > are giving "not a valid month error" > > example is > create or replace function test_bkj(t_date date) > return varchar2 > is > begin > if (t_date < '12-12-2001') > then > t_retval := 'Less Than'; > else > t_retval := 'Greater Than'; > end if; > return t_retval; > end; > > SQL> select test_bkj('12-12-2001') from dual; > select test_bkj('12-12-2001') from dual > * > ERROR at line 1: > ORA-01843: not a valid month > ORA-06512: at "AVLMKT.TEST_BKJ", line 6 > ORA-06512: at line 1 > > when we change the date format in funtion to 'dd/MON/yyyy' keeping the > nls_date_format to 'dd-mm-yyyy' > we are getting wrong results from function . > create or replace function test_bkj(t_date date) > return varchar2 > is > begin > if (t_date < '12-DEC-2001') > then > t_retval := 'Less Than'; > else > t_retval := 'Greater Than'; > end if; > return t_retval; > end; > > SQL> select test_bkj('31-12-2001') from dual; > > TEST_BKJ('31/12/2001') > --------------------------------------------------------------------------- >- ---- > Less Than > > while the results should have been "Greater than" . ............(pl see the > if-else condition ) > its giving "Less Than" for any date comparison. > > Pl help . > > Thanks in advance > > Brajesh Jaiswal -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).