Re: NLS_DATE_FORMAT and Y2K

A copy of this was sent to sumanm_at_my-deja.com (if that email address didn't require changing) On Fri, 17 Sep 1999 23:59:36 GMT, you wrote:

>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.
>

That only does it for the tool SQLPlus. No other applications process a client side login.sql file.

see http://govt.us.oracle.com/~tkyte/Misc/NLSDateFormat.html

for some more info.

>---------------
>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.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Sep 18 1999 - 08:21:06 CDT