Home » SQL & PL/SQL » SQL & PL/SQL » Change NLS DATE FORMAT (Oracle 10.2.0.4.0 ,SunOS)
| 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.
SQL> select sysdate from dual;
SYSDATE
---------
28-DEC-11
I would like to change the date format as below (date and time)
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 #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:39As 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.
|
|
|
|
|
|
|
|
|
|
| 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:49Thank 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.
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.
|
|
|
|
| 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
|
|
|
|
Goto Forum:
Current Time: Fri May 16 22:11:00 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
