| date format showing mm/dd/yyy [message #593374] |
Thu, 15 August 2013 23:50  |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear All,
We have date format in database as "DD/MM/YYYY" and same thing is updated on registry editor.
While accessing the database from other system through pl/sql developer, its showing format as "MM/DD/YYYY".
Registry editor is also updated with "DD/MM/YYYY".
Please assist me how to rectify the problem.
Regards,
Ishika
|
|
|
|
| Re: date format showing mm/dd/yyy [message #593379 is a reply to message #593374] |
Fri, 16 August 2013 00:39  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Quote:We have date format in database as "DD/MM/YYYY" and same thing is updated on registry editor.
I see your OS is windows. Since in your environment, there is no issue with the date format, it means, NLS_DATE_FORMAT in the database initialization parameter and that of the SYSTEM ENVIRONMENT VARIABLES of windows are same. So, no issues here.
Quote:While accessing the database from other system through pl/sql developer, its showing format as "MM/DD/YYYY".
Registry editor is also updated with "DD/MM/YYYY".
You need to understand the overriding precedence of the NLS_DATE_FORMAT. There could be lot of reasons for the incorrect display depending on the overriding precedence.
If registry has the format "DD/MM/YYYY", it is that of OS. When you see in pl/sql developer as "MM/DD/YYYY", it could be that in the tool it is configured in that format.
Precendence could be in the following way -
1. NLS_DATE_FORMAT in the database initialization parameters, overriden by,
2. OS SYSTEM ENVIRONMENT VARIABLES, overriden by,
3. Tool locale specific settings (like pl/sql developer,TOAD etc.), overriden by,
4. Setting it at the oracle client session level: ALTER SESSION SET NLS_DATE_FORMAT='FORMAT', overriden by,
5. Use of the to_date and to_char functions at the individual sql statement.
useful links -
But I want to store the date in format by Ed Stevens
Internal Oracle date format by Dan Morgan
[Updated on: Sun, 02 March 2014 11:04] by Moderator Report message to a moderator |
|
|
|
| Re: date format showing mm/dd/yyy [message #593382 is a reply to message #593374] |
Fri, 16 August 2013 01:17 |
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
|
Senior Member |
|
|
Dear All,
I got the clue -
Since, it is happening from the pl/sql developer. Means problem is from the tools.
below is the solution -
In pl/sql developer -
Tools >> preferences>> date/time
Change the setting and update the date format (by user defined mode).
By doing this, format will get change to "DD/MM/YYYY".
This rectified my problem.
Regards,
Ishika
|
|
|
|
| Re: date format showing mm/dd/yyy [message #593385 is a reply to message #593382] |
Fri, 16 August 2013 01:23 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Good to know that you resolved the issue.
Going forward, remember the overriding precendence in order to avoid such issues. And it is always better to keep the format similar in the DB initialization parameter, OS locale specific setting and the application development tools.
Regards,
Lalit
|
|
|
|
| Re: date format showing mm/dd/yyy [message #593386 is a reply to message #593385] |
Fri, 16 August 2013 01:25 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Quote:And it is always better to keep the format similar in the DB initialization parameter, OS locale specific setting and the application development tools.
You can NEVER ensure this; more, sometimes you CANNOT do that (for instance you have several applications with different formats, and many other cases).
What is better is to NOT rely on any default format in the application.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
