Re: NLS_DATE_FORMAT

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 12 Dec 1999 16:00:05 -0500
Message-ID: <o3385s0j1kmgc4hm1924vmca9lsc80t0u7@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 Sun Dec 12 1999 - 15:00:05 CST