Sorting TIMESTAMP in SQL result is alphabetic

[juanian]

in a SQL Window, I performed a simple unformatted query of all columns on a table that has a column of type TIMESTAMP. After the query completed, I clicked on the sort button to sort that column, and was surprised to see that the values sorted alphabetically, and not by date/time.

Describe shows the column as TIMESTAMP(6), and the results are displayed in '01-DEC-07 01.00.23.642757 AM' format.

Is there a reason that the results were sorted alphabetically? Is there a preference I need to set to fix this?

Note: Sorting on a column of type DATE properly sorts by date, and not alphabetically (displayed in '11/29/1995 1:19:56 AM' format).

PL/SQL Developer version 7.1.4.1390, Oracle 9i 9.2

EDIT: Clarified sorting comment

 

[Marco Kalter]

The timestamp is retrieved as a character string, and as a result it is sorted like this as well. I have added this to the list of enhancement requests.

 

[juanian]

Is there an easy way to have the string for a TIMESTAMP column retrieved in a different format that might be easier to sort (like a '2007/12/31 23:59:59' format) by default? (I'd still like to be able to do a SELECT * without needing to do a to_char() on every TIMESTAMP field.)

It looks like NLS_TIMESTAMP_FORMAT might be able to handle this -- is there a way this can be set for the current session? (I see a setting of DATE and TIME in the Preferences; would this be an appropriate place to add TIMESTAMP too?)

Thanks

 

[Marco Kalter]

You can set the default timestamp formats in the Oracle Registry (NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT), or you can user the ALTER SESSION command to set them.

 

[juanian]

Thanks - executing "alter session set nls_timestamp_format = 'YYYY/MM/DD HH24:MI:SS.FF4';" in my SQL window does the trick. (I avoided the registry change in case some other software (foolishly) assumes that the timestamp format is set to the default format.)

It would be nice if the setting of the TIMESTAMP format could be added to Preferences=>User Interface=>Date/Time. Or, for more flexibility, if some user-defined SQL commands could be executed whenever a SQL window is opened (just a thought!).

Thanks again!

 

[rbrooker]

you can alter the login.sql to automatically execute the alter statement whenever a window ( not sure, but i think of any type ) is opened

 

[juanian]

I decided to try and find some documentation on login.sql, but found none. I did see a post from Marco (in 2005) saying that "AfterConnect.sql is called for all sessions created within PL/SQL Developer. Login.sql is called by the Command Window.". He also said that "We'll add this to the documentation." (tick, tick, tick )

BTW, thanks for the suggestion rbrooker. Using AfterConnect.sql does just what I was looking for!