Re: Setting nls_date_format
- From: "Alberto Dell'Era" <alberto.dellera@xxxxxxxxx>
- To: peter.schauss@xxxxxxx
- Date: Sat, 5 May 2007 14:07:51 +0200
Peter,
look at Metalink note 1048554.6 "How to set NLS_DATE_FORMAT",
it's fairly comprehensive.
From there:
"nls_database_parameters get populated at database creation time,
and cannot be changed."
Be careful also that even if you changed the instance value,
if the client provides its nls_territory, the session nls_data_format
will be set accordingly, overriding your instance setting (that's
what happened to me when I tried your scenario).
You might want to have the app set the value it wants explicitly
at connection time, or use a logon trigger - better yet, change the app
and make it non-dependent on nls_date_format (easier said than done ;).
hth
Alberto
On 5/4/07, Schauss, Peter <peter.schauss@xxxxxxx> wrote:
Another bit of information:
If I look at NLS_INSTANCE_PARAMETERS, I see 'SYYYY-MM-DD HH24:MI:SS',
the value that I expected.
- Peter Schauss
(snip)
I am running Oracle 10.2.0.2.0 on HPUX B.11.23.
We have an application which complains that it is getting the date in
'DD-MON-RR' format.
I changed nls_date_format for the database using:
alter system set nls_date_format = 'SYYYY-MM-DD HH24:MI:SS' scope=
spfile;
and restarted the instance to make the new value effective.
When I issue a "select sysdate from dual;" in sqlplus, I get the
expected date format. I created a pfile and find the expected value
there:
*.nls_date_format='SYYYY-MM-DD HH24:MI:SS'
When I look at the nls_database_parameter view, however, I see
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_DATE_FORMAT DD-MON-RR
What is going on here and how can I set the "permanent" value for
NLS_DATE_FORMAT?
--
Alberto Dell'Era
"dulce bellum inexpertis"
--
//www.freelists.org/webpage/oracle-l
Other related posts:
