Re: NLS_DATE_FORMAT and Y2K

A copy of this was sent to Rich Goldkamp <RJGoldka_at_dcss.com> (if that email address didn't require changing) On Thu, 16 Sep 1999 11:35:20 GMT, you 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?
>

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

>Thanks in Advance,
>Rich

--
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 Thu Sep 16 1999 - 07:03:21 CDT