Re: Setting nls_date_format

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_at_ngc.com> 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"
--
http://www.freelists.org/webpage/oracle-l