Home » Other » General » date format showing mm/dd/yyy (11g, 11.2.0.1, windows server 2008)
date format showing mm/dd/yyy [message #593374] Thu, 15 August 2013 23:50 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Migration from 11g to 12c
Next Topic: Is There Any Site For Online Database for Oracle
Goto Forum:
  


Current Time: Fri Mar 29 03:39:26 CDT 2024