Re: Setting date format in Oracle 8i

From: Manuela Mueller <520040906697-0001_at_t-online.de>
Date: Mon, 08 Oct 2001 00:08:51 +0200
Message-ID: <3BC0D273.D0163460@t-online.de>

>Hello Tom,
>
>I just changed my init.ora file to specify
>a desidere date format thru the parameter
>
>NLS_DATE_FORMAT = DD-MM-YYYY
>
>but after restarting my instance and connecting
>to sqlplus and selecting from a DATE field I still
>see the default date format DD-MON-YY without
>altering any session.
>Should the initialization parameter set the date format
>for all sessions ?
>
>Thanks and regards
>
>Marco

Hello Marco,
generally, there are four different ways to control your nls parameters. In order from lowest to highest priority there is 1. initialization parameters on the server, edit initSID.ora (what you have done)
2. environment variables on the client side (I assume that is your problem)
3. alter session parameters (lasts only for the duration of the sqlplus session)
4. SQL function parameter

for detailed explanations please take a look at http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76966/ch2.htm#745 (create yourself a free account at otn if you don't already have one).

Check your current settings on the client side (eg sqlplus). case 1: without settings on the client side: SQL> select sysdate from dual;  

SYSDATE

---

07-OCT-01 case 2: alter the settings for the current sqlplus session. SQL> alter session set nls_date_format = 'DD-MM-YYYY';  

Session altered.  

SQL> select sysdate from dual;  

SYSDATE

---

07-10-2001

to find out the current the current nls parameters for your sqlplus session

SQL> select * from nls_session_parameters;  

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MM-YYYY <-- just altered via alter

session command!
...

If client and server are on different OS, also take a look at

       http://osi.oracle.com/~tkyte/Misc/NLSDateFormat.html If client and server are on UNIX/LINUX environment, you can edit .profile for the user, eg:
set NLS_DATE_FORMAT = 'DD-MM-YYYY'
export NLS_DATE_FORMAT
please take a look at the URL given above about the impact of initialization parameters (eg NLS_LANGUAGE). maybe this should get you started...
Regards
Manuela Mueller Received on Sun Oct 07 2001 - 17:08:51 CDT