Command window date format

[dkoehne]

Version: PL/SQL Developer 6.0.6.947 (MBCS)
OS: XP SP2

I am trying to change the date format for my command window session without any success.

Here is my test data:

SQL> show user
User is "system"

SQL> select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_DATE_FORMAT DD-MON-RR

SQL> select sysdate from dual;

SYSDATE
-----------
02/02/2006

SQL>
SQL> alter session set nls_date_format = 'DD/MM/YYYY HH24:MI:SS';

Session altered

SQL> select * from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';

PARAMETER VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
NLS_DATE_FORMAT DD/MM/YYYY HH24:MI:SS

SQL> select sysdate from dual;

SYSDATE
-----------
02/02/2006

SQL> select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD/MM/YYYYHH
---------------------------------------------------------------------------
02/02/2006 13:15:10

SQL>

Does anyone have any ideas?

Thanks
Daniel

 

[Marco Kalter]

If a date is formatted on the client, then the format specified in the Date/Time preference is used. By default this is the Windows date format.

 

[David Stidham]

Hello - I know this is an old thread, but this is exactly the issue I'm facing with PL/SQL Developer 9.0.6.1665. I have created the NLS_DATE_FORMAT registry entry and I have created a line in the AfterConnect.sql file to alter the session and set the NLS_DATE_FORMAT to 'MM/DD/YYYY HH24:MI:SS' and absolutely nothing is affecting the Command Window format for dates. I have even done as Daniel did and issued the alter session statement within the command window (successfully) but no change. When I re-launch PL/SQL Developer, and look at the support information, I can "see" the NLS_DATE_FORMAT entry in the Registry tab. However, it hasn't made any difference to the tool.

I can use SQL*Plus and alter my session within that tool to get the time component from the values stored in the DATE data type, but, I'd like to keep it all in the family.

Yes, I have even tried tinkering with the NLS Options in preferences and specifying my desired format for Date as the User Defined choice. That doesn't affect the Command Window.

Within the command window, when I check the NLS Paramter values, I can see that the AfterConnect.sql has fired and the session was altered setting the date format to my requested style. But, the display doesn't change from a date only value.

This may not be a trivial coincidence, but the date format in SQL*Plus, unless I manually execute an alter session myself, is reporting the same NLS_DATE_FORMAT as the database configuration; DD-MON-RR. However, this is not the format being displayed within teh command window. The command window is formatting DD/MM/YYYY.

David

 

[Marco Kalter]

The preferences affect the date format in the Command Window. Setting the NLS_DATE_FORMAT does not have any effect for dates that are formatted on the client, only for SQL and PL/SQL code.

This works fine when I test it in the Command Window. I execute "select sysdate from dual;", change the date format preference, execute the select again, and immediately see the changed format. Note that you may first need to execute a "set datewidth " command if you want to see the time fraction as well.

 

[David Stidham]

Thank you. I've made this adjustment, and returned the preferences for Date display back to Windows and the command window is showing me the full date/time data.

SQL> set datewidth 23
SQL> select sysdate from dual;

SYSDATE
-----------------------
12/12/2012 9:55:00 AM

SQL>

Marco, would it be possible to have the supplied documentation be updated/enhanced to provide more details on the Command Window? I have found a few postings in the forum where there are nuggets of useful information that I can't find in the help documentation. Like the user defined date format should follow Windows formatting syntax and this "set datewidth" feature.

Thank you again for the help.

David