Re: Help with NLS_DATE_FORMAT

From: <mjain_at_my-dejanews.com>
Date: Fri, 05 Mar 1999 08:39:08 GMT
Message-ID: <7bo57b$5u4$1@nnrp1.dejanews.com>

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

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
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Mar 05 1999 - 02:39:08 CST