Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> NLS_DATE_FORMAT problem

NLS_DATE_FORMAT problem

From: Robert Chung <robertch_at_mindspring.com>
Date: Fri, 24 Sep 1999 00:36:15 GMT
Message-ID: <37eac6ff.649548781@news.mindspring.com>

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

<<<<< 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 Received on Thu Sep 23 1999 - 19:36:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US