| NLS_DATE_FORMAT in init.ora [message #69194] |
Tue, 20 November 2001 01:26  |
Pui Hua
Messages: 3
Registered: November 2001
|
Junior Member |
|
|
Hi all,
I am currently using Oracle 8.1.6 and I am facing difficulty in my attempt to change the global date format in Oracle.
I had changed the NLS_DATE_FORMAT the init.ora file by appending this statement at the end of the file:
nls_date_format = 'yyyy-mm-yy hh24:mm:ss'
The database service is then restarted so that the changes will take effect. However, I noticed this setting (date format) only take effects when I logged in as system user; when I logged in as other user, I have to use the "alter session .." command to change the date format dynamically.
Can anyone suggest a way to alter date format to "yyyy-mm-dd" format at database level?
I understand that I can use the TO_DATE function in order to avoid such troubles; However, my program need to be able access both SQL & Oracle databases, thus I have to avoid using database-specific syntax such as TO_DATE or GetDate().
Any hints or suggestions are really appreciated !
Thanks & regards,
Pui Hua
----------------------------------------------------------------------
|
|
|
|
| Re: NLS_DATE_FORMAT in init.ora [message #69250 is a reply to message #69194] |
Thu, 29 November 2001 02:22  |
smk
Messages: 5
Registered: November 2001
|
Junior Member |
|
|
Hi, I think with change in init.ora it should work. I have not tried it, but it should. Other thing you can do to avoid using alter session is try to edit the login.sql on the client side where you are using sql*plus. I think you can write nls_date_format over there. Let me tell you i have not tried, but you can try it out. Do let me know if it works.
Bye
----------------------------------------------------------------------
|
|
|
|
| Re: NLS_DATE_FORMAT in init.ora [message #69292 is a reply to message #69194] |
Tue, 04 December 2001 22:33 |
Pui Hua
Messages: 3
Registered: November 2001
|
Junior Member |
|
|
Hi,
I've tried changing the NLS_DATE_FORMAT in both initORCL.ora and glogin.sql, then shutdown and restart the Oracle database. However, the date format still falls back to the default yy-mon-dd format when I execute a simple get date function from the SQL*Plus.
Have I missed out any steps here ?
Regards.
----------------------------------------------------------------------
|
|
|
|
| Re: NLS_DATE_FORMAT in init.ora [message #69301 is a reply to message #69194] |
Wed, 05 December 2001 21:23 |
Pui Hua
Messages: 3
Registered: November 2001
|
Junior Member |
|
|
Hi SMK,
After restarting the machine (with the glogin.sql and initORCL.ora changed), the changes seems to take effect.
Thanks!
Regards.
----------------------------------------------------------------------
|
|
|
|
| Re: NLS_DATE_FORMAT in init.ora [message #70900 is a reply to message #69250] |
Fri, 09 August 2002 08:18 |
Gene
Messages: 6
Registered: April 2002
|
Junior Member |
|
|
Hi Piu Hua:
I am having similar problem.
What changes you made to glogin.sql ?
I addedd :
alter session set nls_date_format = 'YYYY-MM-DD HH:MI:SS' .
Then SQLPus cannot connect to database.
Thanks for your help.
Gene.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
