Home » SQL & PL/SQL » SQL & PL/SQL » Change date format permanently (10g XE)
Change date format permanently [message #399398] Wed, 22 April 2009 01:25 Go to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
Hi

Current nls date format is RR-MM-dd. i want to change it to dd-MM-YYYY permanently. Alter session command changes only for current session, but how to change it permanently?

Regards
Re: Change date format permanently [message #399401 is a reply to message #399398] Wed, 22 April 2009 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
alter system or modify init.ora file but you shoulfd NOT, NEVER EVER, rely on implicit format, anyone (any DBA) can change it at any time.
And what if you have 2 applications that wants a different default format?
NEVER do that.

Regards
Michel
Re: Change date format permanently [message #399403 is a reply to message #399401] Wed, 22 April 2009 01:56 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
There is no other application on this orcale client and very frankly speaking i'm whole and sole user/ dba whatever u can call it.
I checked init.ora but there is no nls parameter defined. Kindly let me know wht should be changed in init.ora
Re: Change date format permanently [message #399404 is a reply to message #399403] Wed, 22 April 2009 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add it.
But frankly if you start creating an application like this, you do it wrong. Start to create a correct application even if it is for you.

Regards
Michel

[Updated on: Wed, 22 April 2009 02:09]

Report message to a moderator

Re: Change date format permanently [message #399405 is a reply to message #399404] Wed, 22 April 2009 02:09 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
could you pls explain add what?
I tried following commands then restarted oracle. But nls values are unchanged.
ALTER SYSTEM SET NLS_TIMESTAMP_FORMAT='DD-MM-YYYY HH24:MI:SS.FF' SCOPE = SPFILE;
ALTER SYSTEM SET NLS_DATE_FORMAT = 'DDMMYYYY' scope=spfile;
Re: Change date format permanently [message #399413 is a reply to message #399405] Wed, 22 April 2009 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Restart the instance.

Regards
Michel
Re: Change date format permanently [message #399416 is a reply to message #399413] Wed, 22 April 2009 02:44 Go to previous messageGo to next message
NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide
Member
I restarted the oracle service. Is there any other way to restart the instance?
Re: Change date format permanently [message #399419 is a reply to message #399416] Wed, 22 April 2009 02:48 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sqlplus, shutdown and startup.
If you are the administrator of the database you should read Database Concepts and Database Administrator's Guide.

Regards
Michel
Previous Topic: Alter Materialized View log (merged)
Next Topic: range partitioning
Goto Forum:
  


Current Time: Fri Dec 09 01:51:29 CST 2016

Total time taken to generate the page: 0.19017 seconds