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
>
Hmmm ... My feeling is that the problem you have is linked to the
comparisons INSIDE the function, not the way the argument is passed.
Your second example shows rather clearly that an implicit TO_CHAR() is
applied to your argument - and not an implicit TO_DATE() to the
constant. Avoid implicit conversions. Let me also say that I find
extremely dangerous to rely on init.ora settings (or in fact anything
since people can change the format at will through ALTER SESSION). Since
you pass a date to your function (which is as it should be) you should
use explicit conversions in your function and compare the date passed as
argument to TO_DATE('12-12-2001', 'dd-mm-YYYY') or whatever. Ready to
bet that your problems will vanish.
--
Regards,
Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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).
