Re: change default nls_date_format for all sessions

From: <mjain_at_my-dejanews.com>
Date: Wed, 14 Apr 1999 22:41:05 GMT
Message-ID: <7f35i0$3tb$1@nnrp1.dejanews.com>

Hi I had answered this qustion earlier and I am attaching the same message here.

I hope this helps,

Manoj Jain
Chauncey Certified Oracle7 DBA
Oracle Certified Professional - DBA
Email Address : m_jain_at_go.com

--------------Original Message Containing Answer To Your Question --->

Hi Manoj

Thanks for the explanation it all makes sense now. Our client application now works as we want it too. Thanks for the help.

Regards

Alistair Thomson

-----Original Message-----
From: mjain_at_my-dejanews.com [mailto:mjain_at_my-dejanews.com] Sent: 05 March 1999 08:39
To: alt_at_interchange.co.uk
Subject: Re: Help with NLS_DATE_FORMAT

Hi,

You need to set NLS_DATE_FORMAT at each client machine.

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.

For NT Client:--

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

1. Start->Run->regedt32
2. HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE. Highlight/Select ORACLE
3. 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> You can check your NLS_DATE_FORMAT for your session using following query:

SELECT * FROM NLS_SESSION_PARAMETERS; Hope this helps,

Manoj Jain
Chauncey Certified Oracle7 DBA
Oracle Certified Professional - DBA

Email Address : m_jain_at_go.com

In article <7bmeqh$m2g$1_at_nnrp1.dejanews.com>,   alt_at_interchange.co.uk wrote:
> Oracle 7.3.2.3
> sqlnet 2.3.4
> decalpha running decunix
>
> Any idea whats going on here?
>
> We've got NLS_DATE_FORMAT="YYYYMMDD" set in the init.ora file and when you
use
> sqlplus on the server it returns the date in the correct format eg
>
> select sysdate from dual;
>
> sysdate
> ---------
> 19990304
>
> When you use sqlplus from the client you get a different result!
>
> select sysdate from dual;
>
> sysdate
> ---------
> 03-MAR-99
>
> Could someone please explain why this is happening. It's causing havoc
with
> our client application which expects the date in the YYYYMMDD format.
>
> I'd be grateful for a reply to alt_at_interchange.co.uk
>
> Thanks for the help.
>
> Al

In article <7f31ks$gk$1_at_nnrp1.dejanews.com>,   tienlee_at_mailexcite.com wrote:
> I believe the same question has been asked several times but there was
> no definite answer.
>
> I included nls_date_format = "DD-MON-RR" in my init.ora file but when
> I start a new session, the default date format is "DD-MON-YY" confirmed by
> querying nls_session_parameters and nls_database_parameters. Doing
> a "alter session set nls_date_format = "DD-MON-RR"" works but I want a
> more permanent setting.
>
> My question to the Oracle DBA guru is how to set the date format for
> all startup sessions.
>
> Please help. Thanks.
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Apr 14 1999 - 17:41:05 CDT