OraFAQ Forum: SQL & PL/SQL » change date format

Home » SQL & PL/SQL » SQL & PL/SQL » change date format (10g express)

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

change date format [message #481999]

Tue, 09 November 2010 00:24

elaiyavel
Messages: 114
Registered: April 2008

Senior Member

Hi,

I want to change the date format at database level in 10g express edition.

When i try to run the command,

ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY',
it throws error like specified initialisation parametr is not modifiabale with this option.

what are the other ways to chnage the database date format to the desired one.?

Thanks,
Elaiyavel .

Report message to a moderator

Re: change date format [message #482001 is a reply to message #481999]

Tue, 09 November 2010 00:35

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

Senior Member
Account Moderator

ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY' SCOPE=SPFILE
and restart.
But there should be no reason to do so; explain why you need this.

Regards
Michel

[Edit: a word missing]

[Updated on: Tue, 09 November 2010 01:27]

Report message to a moderator

Re: change date format [message #482009 is a reply to message #482001]

Tue, 09 November 2010 01:13

Frank
Messages: 7901
Registered: March 2000

Senior Member

Misread Michel's reply. Removed non-sense response to this.

[Updated on: Tue, 09 November 2010 01:15]

Report message to a moderator

Re: change date format [message #482014 is a reply to message #482009]

Tue, 09 November 2010 01:32

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, one option (if it satisfies your needs) would be to set an environment variable:

C:\>set nls_date_format=dd/mm/yyyy

C:\>sqlplus scott/tiger@ora10

SQL*Plus: Release 10.2.0.1.0 - Production on Uto Stu 9 08:31:30 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select sysdate from dual;

SYSDATE
----------
09/11/2010

SQL>

Report message to a moderator

Re: change date format [message #482016 is a reply to message #482014]

Tue, 09 November 2010 01:42

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

Senior Member
Account Moderator

Well, this is for the session not for the instance (database as OP said).
We have to wait for OP's answer to my question but I think that behind his is that there should be no need to change the client configuration.

Regards
Michel

Report message to a moderator

Re: change date format [message #482019 is a reply to message #482016]

Tue, 09 November 2010 02:41

elaiyavel
Messages: 114
Registered: April 2008

Senior Member

The 3rd party applicatuon that is used to write data to oracle database is using the date format 'DD/MM/YYYY',

I need to set this format in the database, so that no error is thrown. We are not allowed to convert the date formats in 3rd party application.

Report message to a moderator

Re: change date format [message #482022 is a reply to message #482019]

Tue, 09 November 2010 02:54

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

Senior Member
Account Moderator

1/ The third party application is badly written
2/ Do as I said or...
3/ Create a logon trigger that set the format or...
4/ If the third party application is a 3-tier one, then set the NLS_DATE_FORMAT environment parameter on the application server as Littlefoot said.

Regards
Michel

Report message to a moderator

Re: change date format [message #482161 is a reply to message #482022]

Wed, 10 November 2010 03:26

elaiyavel
Messages: 114
Registered: April 2008

Senior Member

i have set the date format at session level and continuing my development.

ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY' SCOPE=SPFILE -- is successful
i donot have admin access to restart the database to have the change successful. I have raised the request for admin access. will keep you posted.

Report message to a moderator

Re: change date format [message #482165 is a reply to message #482161]

Wed, 10 November 2010 03:50

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Heh? You can ALTER SYSTEM but can not restart the database?

Report message to a moderator

Re: change date format [message #482166 is a reply to message #482165]

Wed, 10 November 2010 03:53

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

whats wrong on that ?

@op
Quote:

But there should be no reason to do so; explain why you need this.

sriram

[Updated on: Wed, 10 November 2010 03:54]

Report message to a moderator

Re: change date format [message #482176 is a reply to message #482166]

Wed, 10 November 2010 04:22

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If you asked me (about what would be wrong about my previous message), here you are: I thought that, someone who has a privilege to alter a system (i.e. is powerful enough) should also be able to restart the database. From my point of view (which doesn't have to be correct), altering a system can do more harm than shutdown/startup operation.

Report message to a moderator

Re: change date format [message #482178 is a reply to message #482166]

Wed, 10 November 2010 04:26

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

ramoradba wrote on Wed, 10 November 2010 09:53

whats wrong on that ?

Well, its a surprise to me that a priv as powerful as alter system is available outside the DBA team, doubly so given that a number of things you can do with it (like this exact example) need a restart to take effect.

Just my 2 cents, but I raised my eyebrow at that one too.

Edit: slow browser, LF beat me to it Smile

Smile

[Updated on: Wed, 10 November 2010 04:27]

Report message to a moderator

Re: change date format [message #482180 is a reply to message #482176]

Wed, 10 November 2010 04:28

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Quote:

I thought that, someone who has a privilege to alter a system (i.e. is powerful enough) should also be able to restart the database.

No.
Quote:

From my point of view (which doesn't have to be correct), altering a system can do more harm than shutdown/startup operation.

In some cases true but not all cases...

But the actual answer to OP was given by Michel already.

sriram

Report message to a moderator

Re: change date format [message #482640 is a reply to message #482180]

Mon, 15 November 2010 03:07

elaiyavel
Messages: 114
Registered: April 2008

Senior Member

sorry to activate a old post,

I have re-started the database,but still i'm unable to get the correct format.
Ran the command;
alter system set nls_date_format ='DY Mon DD HH24:MI:SS YYYY' scope=spfile;
re-started the database:
I'm getting date in the old format as "dd-mm-yyyy"

Report message to a moderator

Re: change date format [message #482651 is a reply to message #482640]

Mon, 15 November 2010 03:54

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Then the application is overriding the data format, and changing it at database level doesn't help you.

Have you tried setting the nls_date_format environment variable yet, as Littlefoot suggested?

Report message to a moderator

Re: change date format [message #482652 is a reply to message #482640]

Mon, 15 November 2010 03:56

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

Your Question was answered Here

Report message to a moderator

Re: change date format [message #482653 is a reply to message #482640]

Mon, 15 November 2010 03:57

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

Senior Member
Account Moderator

Did you try what format you get at SQL*Plus level?

Regards
Michel

Report message to a moderator

Re: change date format [message #482654 is a reply to message #482652]

Mon, 15 November 2010 03:57

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:351017764854

Report message to a moderator

Previous Topic:	Regarding Execute immediate passing Schema name
Next Topic:	cross join query to remove repeating combination

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Aug 22 16:29:20 CDT 2025