Re: NLS_DATE_FORMAT problem

A copy of this was sent to robertch_at_mindspring.com (Robert Chung) (if that email address didn't require changing) On Fri, 24 Sep 1999 00:36:15 GMT, you wrote:

>
>
>I am having problem resetting NLS_DATE_FORMAT parameter on my Oracle
>database running in HP/UX box. I changed it to nls_date_format
>="DD-MON-YYYY" in initSID.ora and restarted database, but "select
>sysdate from dual" is showing only two-digits for year. sqlplus
>login.sql or glogin.sql do not have anything that changes
>NLS_DATE_FORMAT. According to output below (from
>nls_instance_parameters and nls_session_parameters), I can see
>database instance did accept new NLS_DATE_FORMAT, but apparently
>something is overwriting it whenever I start sqlplus session. What
>would that be? Someone please let me know where to look. By the way,
>alter session set nls_date_format= "DD-MON-YYYY" inside sqlplus always
>works. Thank you.
>
>
>
><<<<< UNIX ENVIRONMENT SETTING >>>>>
>NLS_LANG=American_America.WE8ISO8859P1
>ORA_NLS32=/opt/oracle/oracle804/ocommon/nls/admin/data
>
>

see the url in my signature for why this doesn't work.

it is because you have set the NLS_LANG in short...

>
><<<<< OUTPUT FROM SQLPLUS >>>>>
>21:51:22 SQL> select * from props$;
>
>NAME
>------------------------------
>VALUE$
>----------------------------------------------------------------------------
>----------------------------------------------------------------
>COMMENT$
>----------------------------------------------------------------------------
>----------------------------------------------------------------
>DICT.BASE
>2
>dictionary base tables version #
>
>NLS_LANGUAGE
>AMERICAN
>Language
>
>NLS_TERRITORY
>AMERICA
>Territory
>
>NLS_CURRENCY
>$
>Local currency
>
>NLS_ISO_CURRENCY
>AMERICA
>ISO currency
>
>NLS_NUMERIC_CHARACTERS
>.,
>Numeric characters
>
>NLS_CALENDAR
>GREGORIAN
>Calendar system
>
>NLS_DATE_FORMAT
>DD-MON-YY
>Date format
>
>NLS_DATE_LANGUAGE
>AMERICAN
>Date language
>
>NLS_CHARACTERSET
>WE8ISO8859P1
>Character set
>
>NLS_SORT
>BINARY
>Linguistic definition
>
>NLS_NCHAR_CHARACTERSET
>WE8ISO8859P1
>NCHAR Character set
>
>NLS_RDBMS_VERSION
>8.0.4.2.1
>RDBMS version for NLS parameters
>
>GLOBAL_DB_NAME
>ODYPLAN.WORLD
>Global database name
>
>EXPORT_VIEWS_VERSION
>7
>Export views revision #
>
>
>15 rows selected.
>
>Elapsed: 00:00:00.01
>21:51:22 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-MON-YY
>NLS_DATE_LANGUAGE AMERICAN
>NLS_SORT BINARY
>
>9 rows selected.
>
>Elapsed: 00:00:00.01
>21:51:22 SQL> select * from nls_instance_parameters;
>
>PARAMETER VALUE
>------------------------------ ------------------------------
>NLS_LANGUAGE AMERICAN
>NLS_TERRITORY AMERICA
>NLS_SORT BINARY
>NLS_DATE_LANGUAGE
>NLS_DATE_FORMAT DD-MON-YYYY
>NLS_CURRENCY
>NLS_NUMERIC_CHARACTERS .,
>NLS_ISO_CURRENCY
>NLS_CALENDAR
>
>9 rows selected.
>
>Elapsed: 00:00:00.01
>21:51:22 SQL> select * from nls_database_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-MON-YY
>NLS_DATE_LANGUAGE AMERICAN
>NLS_CHARACTERSET WE8ISO8859P1
>NLS_SORT BINARY
>NLS_NCHAR_CHARACTERSET WE8ISO8859P1
>NLS_RDBMS_VERSION 8.0.4.2.1
>
>12 rows selected.
>
>Elapsed: 00:00:00.00
>21:51:22 SQL> select sysdate from dual;
>
>SYSDATE
>---------
>23-SEP-99
>
>Elapsed: 00:00:00.00
>
>
>
>
>
>
>
>
>
>
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 23 1999 - 20:08:18 CDT