Search: For:
Browsing Single Category
www.dbatoolz.com ORACLE DBA Forums Forum › Topic Id: 2594 | Permalink

nls_date_format not reflecting

Topic ID: 2594
Created By: 2007-SEP-06 08:07:06 [Dba_Giri]
Updated By: 2007-SEP-06 13:55:33 [Vitaliy]
Status: Open
Severity: Normal
Read Only: No
8194
2007-SEP-06 08:07:06
User
 
 
Registered On: Jul 2007
Total Posts: 7
I have changed the nls_date_format as mentioned below:
Alter system set nls_date_format='YYYYMMDD' scope=spfile
My Server is started using the spfile and after the above command execution, I 
have restarted the server also,
even though am not getting the date format as YYYYMMDD.
I have checked it using the following
Select Sysdate from dual;
It is showing the output in default date dormat i.e DD-MON-YY
I have seen the nls_date_format as using the following 
show parameter nls_date_format
it is given the o/p as YYYYMMDD
can you please let me know problem might be what..?

Thanks a lot in advance..
Giri K.Y.
8197
2007-SEP-06 13:53:16
Moderator
 
 
Registered On: Mar 2006
Total Posts: 233
##
## nls_database_parameters are the values that were set during database creation
##

12:37:24 SYSTEM@10R2:oracle> col value format a30
12:37:35 SYSTEM@10R2:oracle> set lines 132
12:37:37 SYSTEM@10R2:oracle> set trims on
12:37:40 SYSTEM@10R2:oracle> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.1.0.4.2

20 rows selected.



##
## nls_instance_parameters are the values set in the init.ora or spfile
## NOTE:
##    nls_instance_parameters values overwrite nls_database_parameters
##


12:38:03 SYSTEM@10R2:oracle> col parameter format a30
12:38:23 SYSTEM@10R2:oracle> select * from nls_instance_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_SORT
NLS_DATE_LANGUAGE
NLS_DATE_FORMAT
NLS_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_ISO_CURRENCY
NLS_CALENDAR
NLS_TIME_FORMAT
NLS_TIMESTAMP_FORMAT

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
NLS_DUAL_CURRENCY
NLS_COMP
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.



##
## nls_session_parameters are the values set at the session level
##
## NOTE:
##    nls_session_parameters overwrite BOTH 
##       nls_instance_parameters and
##       nls_database_parameters values
##


12:38:28 SYSTEM@10R2:oracle> 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-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

12:38:46 SYSTEM@10R2:oracle>

REFERENCE:
   Note:1048554.6 How to set NLS_DATE_FORMAT
   Note:241047.1  The Priority of NLS Parameters Explained


Run all of the above queries on your system and it should be clear why your 
setting at the SPFILE is not taking presence.

- Vitaliy
[edited by: Vitaliy at 13:55 (CST) on Sep. 06, 2007]