Xref: alice comp.databases.oracle.server:77516 Path: alice!news-feed.fnsi.net!enews.sgi.com!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail From: Thomas Kyte Newsgroups: comp.databases.oracle.server Subject: Re: NLS_DATE_FORMAT Date: Sun, 12 Dec 1999 16:00:05 -0500 Organization: Oracle Service Industries Lines: 57 Message-ID: References: <830s09$2jl$1@centipede.wantree.com.au> Reply-To: tkyte@us.oracle.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Trace: inet16.us.oracle.com 945032446 20520 130.35.34.225 (12 Dec 1999 21:00:46 GMT) X-Complaints-To: usenet@inet16.us.oracle.com NNTP-Posting-Date: 12 Dec 1999 21:00:46 GMT X-Newsreader: Forte Agent 1.7/32.534 A copy of this was sent to "Ezzedin Habib" (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@8i> alter session set nls_date_format='dd-mon-yyyy'; Session altered. tkyte@8i> select to_date( '01-jan-99' ) from dual; TO_DATE('01 ----------- 01-jan-0099 tkyte@8i> alter session set nls_date_format='dd-mon-rrrr'; Session altered. tkyte@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@us.oracle.com Oracle Service Industries Reston, VA USA Opinions are mine and do not necessarily reflect those of Oracle Corporation