change default nls_date_format for all sessions

[tie...@mailexcite.com]

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

[mj...@my-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_j...@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: mj...@my-dejanews.com [mailto:mj...@my-dejanews.com]
Sent: 05 March 1999 08:39
To: a...@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_j...@go.com

In article <7bmeqh$m2g$1...@nnrp1.dejanews.com>,
a...@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 a...@interchange.co.uk
>
> Thanks for the help.
>
> Al

In article <7f31ks$gk$1...@nnrp1.dejanews.com>,