Re: NLS_DATE_FORMAT

Thank you Thomas. But I can not use RRRR because the system I am running is an HR system and there is a good possibility to have a birth date before 1950.
The next thing I tried was adding NLS_LANG (AMERICAN_AMERICA.WE8ISO8859P1) and NLS_DATE_FORMAT (DD-MON-YYYY) entries into the registry and still works fine with SELECT statements but not with INSERT or UPDATE.

The other strange behavior is that I have added the same registry entries in the client machines (I have 8 clients all running Windows 95 and using SQL*Net 2.3); on one of the clients I get an error when attempting to INSERT/UPDATE a date column with a value in DD-MON-YY format (in SQL*Plus), but not on the other 7 and the value in those 7 is stored as dd-mon-00yy. I just can not figure it out.

By the way ALTER SESSION ... works fine in all the clients and the server.

Any advise is highly appreciated.

Regards,
Ezzedin Habib

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:o3385s0j1kmgc4hm1924vmca9lsc80t0u7_at_4ax.com...
> A copy of this was sent to "Ezzedin Habib" <ehabib_at_wantree.com.au>
> (if that email address didn't require changing)
> On Mon, 13 Dec 1999 15:11:10 +0800, you wrote:
>
> >I am running Oracle Workgroup Server version 7.3.4.1.1 on a Windows NT
 4.0
> >server with service pack 5.
> >
> >In an attempt to set the date format to DD-MON-YYYY, I added the
 following:
> >NLS_DATE_FORMAT="DD-MON-YYYY" to the init.ora file restarted the database
> >but still it would accept and display dates in DD-MON-YY format. Next, I
> >removed the "My Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\NLS_LANG
> >registry entry and the database would display dates in DD-MON-YYYY format
> >but would still accept DD-MON-YY in an insert or update and store the
 date
> >value as DD-MON-00YY.
> >
> >Has anybody encountered this problem and can anybody please tell how to
> >solve it. In all my UNIX installation all I had to do is the change in
 the
> >init.ora file and everything goes well. Thank you.
> >
> >Regards,
> >Ezzedin Habib
> >
>
>
> 1) see http://osi.oracle.com/~tkyte/Misc/NLSDateFormat.html for some info.
>
> 2) you might want to use RRRR instead of YYYY.
>
>
> tkyte_at_8i> alter session set nls_date_format='dd-mon-yyyy';
>
> Session altered.
>
> tkyte_at_8i> select to_date( '01-jan-99' ) from dual;
>
> TO_DATE('01
> -----------
> 01-jan-0099
>
> tkyte_at_8i> alter session set nls_date_format='dd-mon-rrrr';
>
> Session altered.
>
> tkyte_at_8i> select to_date( '01-jan-99' ) from dual;
>
> TO_DATE('01
> -----------
> 01-jan-1999
>
> --
> See http://osi.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 Tue Dec 14 1999 - 00:00:00 CST