Re: NLS_DATE_FORMAT and Y2K

on NT, you can set the NLS_DATE_FORMAT in the registry- hkey_local_machine/software/oracle
Just add a new string value called nls_date_format and set its value to whatever you need. This also works on 95 and 98 clients. If I remember correctly, a client specified nls_date_format will always override any format you have set at the instance level.

On Unix, you can try setting an environment variable for your Oracle owner.
Example: export NLS_DATE_FORMAT="mm-dd-rrrr hh24:mi:ss"

I have never had any problem setting my nls_date_format in my init<sid>.ora

Hope this helps,

Denzil

In article <7rqkln$5fi$1_at_nnrp1.deja.com>,   Rich Goldkamp <RJGoldka_at_dcss.com> wrote:
> Can the NLS_DATE_FORMAT parameter be set at the instance level???
>
> When I execute the following query, it returns an error when run
> against an instance where the NLS_DATE_FORMAT parameter has been set.
>
> SELECT TO_DATE( DECODE( dummy, 'N', 'X',
> TO_DATE( '29-FEB-2000', 'DD-MON-YYYY' ) ) )
> FROM DUAL;
>
> ERROR at line 1:
> ORA-01839: date not valid for month specified
>
> If I execute,
>
> ALTER SESSION SET NLS_DATE_FORMAT="DD-MON-YYYY";
>
> then my above date query correctly returns this:
>
> TO_DATE(DEC
> -----------
> 29-FEB-2000
>
> However, if I add a line to my parameter file like this:
>
> NLS_DATE_FORMAT="DD-MON-YYYY"
>
> and bounce my instance, I still get the above listed ORA-01839 error.
> If I query V$PARAMETER, the server reports that the parameter is set
> (I've tried double, single, and no quotes around the date format
> specifier). The error is only corrected when I set the parameter at
> the session level (or with an environment variable at the desktop
> level). The obvious problem is that I don't want to have to implement
> the solution at the session or desktop level.
>
> Has anyone found a way to set NLS_DATE_FORMAT at the instance level,
> and have it work correctly?
>
> Thanks in Advance,
> Rich
> --
> Rich Goldkamp, Consultant, OCP RJGoldka_at_dcss.com
> Digital Consulting & Software Services (504) 523-5005 x2736
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Sep 16 1999 - 15:26:47 CDT