Re: nls_date_format in initSID.ora

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 31 Aug 2001 16:08:16 -0500
Message-ID: <usne7hiyj.fsf@verizon.net>

On Fri, 31 Aug 2001, simon.pospisil_at_fastsearch.com wrote:

> Hello, I've tried changing the nls_date_format variable from:
> nls_date_format = DD-MON-YYYY
> to
> nls_date_format = YYYY-MM-DD
>
> Then I restart the database but it will only use the old
> format, the new format has not been recognised.

Okay, I played around with this a bit and got the same behavior as Simon. 8.1.7, win2K.

I thought Simon might have missed that quotation marks are required in defining the format.

I tried both:

    nls_date_format = YYYY-MM-DD and
    nls_date_format = "YYYY-MM-DD"
and got the same behaviour.

,----[ MY RESULT ]

| SQL> select sysdate from dual;
| 
| SYSDATE
| ---------
| 31-AUG-01

`----

I didn't have any environmental variables set that would override the server settings and I didn't alter anything on the session as well.

FWIW, here is the nls_date_format parameter as selected from the nls_***_parameters tables when I did my tests.

SQL> select * from nls_instance_parameters where PARAMETER = 'NLS_DATE_FORMAT';

PARAMETER VALUE

------------------------------ ----------------------------------------
NLS_DATE_FORMAT 	       YYYY-MM-DD

SQL> select * from nls_database_parameters where PARAMETER = 'NLS_DATE_FORMAT';

PARAMETER VALUE

------------------------------ ----------------------------------------
NLS_DATE_FORMAT 	       DD-MON-RR

SQL> select * from nls_session_parameters where PARAMETER = 'NLS_DATE_FORMAT';

PARAMETER VALUE

------------------------------ ----------------------------------------
NLS_DATE_FORMAT 	       DD-MON-RR

> I'm editing the initSID.ora file where SID is the sid of the
> database in question. Is that right?

Yes.

> However, if I alter the format within a session then it works
> fine.

When I alter the session, I get the correct behavior as well.

,----[ MY RESULT ]

| SQL> alter session set nls_date_format = "YYYY-MM-DD";
| 
| Session altered.
| 
| SQL> select sysdate from dual;
| 
| SYSDATE
| ----------
| 2001-08-31

`----

-- 
Galen Boyer
It seems to me, I remember every single thing I know.

Received on Fri Aug 31 2001 - 16:08:16 CDT