Re: nls_date_format in 8.1.6

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: sfaroult_at_oriole.com

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: ListGuru_at_fatcity.com (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).