Re: NLS_DATE_FORMAT (7.3.4 on NT)

From: <mjain_at_my-dejanews.com>
Date: Sat, 06 Feb 1999 09:32:17 GMT
Message-ID: <79h271$ise$1@nnrp1.dejanews.com>

Hi,

NLS_DATE_FORMAT should be changed at each client machine and INITIALIZATION parameter NLS_DATE_FORMAT has no effect on the client's default date format.

If you use Windows NT then you need to change the NLS_DATE_FORMAT in Windows Registry. Here is how you do it:-

Start->Run->regedt32
HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE. Highlight/Select ORACLE
Menu->Edit->Add Value..

3.1 Add Value Window

Value Name : NLS_DATE_FORMAT
Data Type : REG_EXPAND_SZ ( Choose this from list. )

3.2 String Editor Window

String : YYYY-MM-DD HH24:MI:SS

NLS_DATE_FORMAT Initialization Parameter has no effect on the default date format of clients.
The registry key NLS_DATE_FORMAT should be set on each server and client for changing the default format.

Now issue following query at your client machine using SQL-Plus:--

SQL> select sysdate from dual;

SYSDATE

1999-02-02 09:54:41

SQL> Alternate Method:

Alternatively in SQL*PLUS following command could be used:

SQL>ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; Session altered.

SQL> select sysdate from dual;

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; Session altered.

SQL> select sysdate from dual;

SYSDATE

1999-02-02 09:48:06

SQL> Hope this helps,

Manoj Jain
Chauncey Certified Oracle7 DBA
Oracle Certified Professional - DBA

In article <918236260.28359.0.nnrp-04.c2de11b4_at_news.demon.co.uk>, "Ian Boston" <ieb_REMOVE_TO_SEND__at_time-fld._REMOVE_TO_SEND_.demon.co.uk> wrote:
> Hi,
> I have a problem with NLS_DATE_FORMAT as a parameter in an init.ora file for
> Oracle 7.3.4 on NT.
>
> if I set
> NLS_DATE_FORMAT = "YYYY-MON-DD"
> in the init.ora file, and start the database when I do a select on a table
> (ie select date_field form date_table; ) I get
>
> 25-JUN-98
>
> if I do an
>
> alter session set NLS_DATE_FORMAT = "YYYY-MON-DD";
>
> then I get the correct date
>
> 1998-JUN-25
>
> To rule a few thing out.
> 1. If I set NLS_DATE_FORMAT to an invalid string the database wont start.
> 2. When I look in the instance manager gui, the NLS_DATE_FORMAT parameter
> matches what I set it to.
> 3. If I start the database manually in SVRMGR I dont get any complaints
> about the init file.
> 4. Setting NLS_DATE_LANGUAGE as a parameter also has no effect
>
> Any Ideas by email would be greatly appreciated. Sorry if this has come up
> before, I haven't posted here before and I couldn't see any current
> messages.
>
> Ian Boston
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Sat Feb 06 1999 - 03:32:17 CST