OraFAQ Forum: SQL & PL/SQL » Change NLS DATE FORMAT

Home » SQL & PL/SQL » SQL & PL/SQL » Change NLS DATE FORMAT (Oracle 10.2.0.4.0 ,SunOS)

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

Change NLS DATE FORMAT [message #537381]

Wed, 28 December 2011 01:04

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Hi,

Current date format in database session is as below.

Code: [Select all] [Show/ hide]

SQL> select sysdate from dual;

SYSDATE
---------
28-DEC-11

I would like to change the date format as below (date and time)

Code: [Select all] [Show/ hide]

TO_CHAR(SYSDATE,'DD-M
---------------------
28-dec 01:09

I have a question regarding this date format change.

Would there be any impact to database if i change the NLS DATE FORMAT as above example (date and time)
at database level /system level ?

Thank you

[Updated on: Wed, 28 December 2011 01:10]

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537384 is a reply to message #537381]

Wed, 28 December 2011 01:22

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

Senior Member
Account Moderator

Quote:

Would there be any impact to database if i change the NLS DATE FORMAT as above example (date and time)
at database level /system level ?

Of course! the other (bad) applications and scripts are like you, they use implicit format and conversion.

Regards
Michel

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537386 is a reply to message #537381]

Wed, 28 December 2011 01:25

muralikri
Messages: 638
Registered: August 2011
Location: chennai

Senior Member

No changes... Check the sample code.

Code: [Select all] [Show/ hide]

begin
execute immediate 'alter session set nls_date_format = ''dd.mm.yyyy hh24:mi:ss'' ';
insert into test values (sysdate);
commit;
end;
/
select sysdate  from dual;

12/28/2011 12:55:22 PM

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537391 is a reply to message #537384]

Wed, 28 December 2011 01:39

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Murali,

Quote:

No changes

Can you please complete your sentence ?

Quote:

Of course! the other (bad) applications and scripts are like you

As per Michel's answer, i would say that it is a bad practice to change the date format .

Michel,

Do you mean to say we must not change the date format at session/database/system level ?

[Updated on: Wed, 28 December 2011 01:40]

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537394 is a reply to message #537391]

Wed, 28 December 2011 02:11

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Jack14 wrote on Wed, 28 December 2011 08:39

As per Michel's answer, i would say that it is a bad practice to change the date format .

Not really, it only says that it is a bad idea to depend on concrete NLS_DATE_FORMAT value. Why do you want to change it on database level? The most probable reason is stated in the first sentence of this paragraph, which implies that your application is badly written. No reason to suppose that the other applications running now without issues (or supposed to run in future) do not have such bugs.

Of course the only way (apart from inspecting all application code running on the database) to check impact of changing NLS_DATE_FORMAT value is to change it ... and wait for users' complaints.

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537395 is a reply to message #537391]

Wed, 28 December 2011 02:11

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

Senior Member
Account Moderator

Yes.
Use a format when you want a specific format.

Do NOT use what muralikri posted it is a bad idea (and it requires you change your code as you can see it added an "execute immediate").

Regards
Michel

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537511 is a reply to message #537395]

Thu, 29 December 2011 01:49

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Thank you Flyboy and Michel,

Quote:

Of course the only way (apart from inspecting all application code running on the database) to check impact of changing NLS_DATE_FORMAT value is to change it ... and wait for users' complaints.

Use a format when you want a specific format.

Based on your above comments , i will change the NLS_DATE_FORMAT using below command at session.

Code: [Select all] [Show/ hide]

ALTER SESSION SET NLS_date_format = 'YYYY-MM-DD HH24:MI:SS';

I hope changing at session level will not impact database.

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537512 is a reply to message #537511]

Thu, 29 December 2011 01:55

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

Senior Member
Account Moderator

Wait and see... /forum/fa/3971/0/

Anyway, thanks for the feedback.

Regards
Michel

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537578 is a reply to message #537511]

Thu, 29 December 2011 08:03

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Jack14 wrote on Thu, 29 December 2011 02:49

Thank you Flyboy and Michel,

Quote:
Of course the only way (apart from inspecting all application code running on the database) to check impact of changing NLS_DATE_FORMAT value is to change it ... and wait for users' complaints.

Use a format when you want a specific format.

Based on your above comments , i will change the NLS_DATE_FORMAT using below command at session.
Code: [Select all] [Show/ hide]
ALTER SESSION SET NLS_date_format = 'YYYY-MM-DD HH24:MI:SS';
I hope changing at session level will not impact database.

It depends on the poor coding by developers. I'll bet some other things that work now will break. There are always sloppy and lazy developers who use strings as DATEs, who just never learn, and these will now fail.

Report message to a moderator

Re: Change NLS DATE FORMAT [message #537579 is a reply to message #537512]

Thu, 29 December 2011 08:05

venkat5b4
Messages: 2
Registered: December 2011
Location: Hyderabad

Junior Member

hi..,
Actually you have NLS Parameters at 3 levels..
1.Database level
2.Instance level
3.Session level

Session level parameters have domination over Instance level and database level parameters and
instance level parameters have domination over database level parameters.

For more details go through these tables nls_database_parameters,nls_instance_parameters,nls_session_parameters

Report message to a moderator

Previous Topic:	Pivot and similar!
Next Topic:	Mark each group in sequence (merged 4)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 04:48:33 CDT 2025