Re: NLS_DATE_FORMAT and Y2K

From: <sumanm_at_my-deja.com>
Date: Fri, 17 Sep 1999 23:59:36 GMT
Message-ID: <7rukl3$3d5$1@nnrp1.deja.com>

I have successfully set my instance level NLS_DATE_FORMAT, by adding this line in my C:\ORANT\DBS\LOGIN.SQL file
(You may create a login.sql file in your c:\orawin95\dbs\ directory).
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY';

Hope that helps.

---

Suman Mehta

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 Fri Sep 17 1999 - 18:59:36 CDT