OraFAQ Forum: SQL & PL/SQL » Change date format permanently

Home » SQL & PL/SQL » SQL & PL/SQL » Change date format permanently (10g XE)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Change date format permanently [message #399398]

Wed, 22 April 2009 01:25

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

Report message to a moderator

Re: Change date format permanently [message #399401 is a reply to message #399398]

Wed, 22 April 2009 01:44

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Change date format permanently [message #399403 is a reply to message #399401]

Wed, 22 April 2009 01:56

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

Report message to a moderator

Re: Change date format permanently [message #399404 is a reply to message #399403]

Wed, 22 April 2009 02:05

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

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

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;

Report message to a moderator

Re: Change date format permanently [message #399413 is a reply to message #399405]

Wed, 22 April 2009 02:42

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Restart the instance.

Regards
Michel

Report message to a moderator

Re: Change date format permanently [message #399416 is a reply to message #399413]

Wed, 22 April 2009 02:44

NewToOracle10g
Messages: 34
Registered: April 2009
Location: Adelaide

Member

I restarted the oracle service. Is there any other way to restart the instance?

Report message to a moderator

Re: Change date format permanently [message #399419 is a reply to message #399416]

Wed, 22 April 2009 02:48

Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Previous Topic:	Alter Materialized View log (merged)
Next Topic:	range partitioning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jul 26 02:14:10 CDT 2025