Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: nls_date_format in initSID.ora
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