Date Sort is wrong

[Baila_me]

Hello,
if i want sort a date the Developer sorted it wrong, Only the day is correctly sorted bur not the moth.
What can I do, that the Developer sort the Date correctly?

 

[mobailey]

I'm guessing that you are converting a date to a string, in which case PL/SQL is sorting it (correctly) as a string.

e.g.1:

SELECT TO_CHAR(, 'dd.mm.yyyy') FROM

;

The above would give the sort order and format that your screenshot shows.

e.g.2:

SELECT TO_DATE(TO_CHAR(, 'dd.mm.yyyy'), 'dd.mm.yyyy') FROM

;

The above (fairly nonsensical SQL) would tell PL/SQL Developer to treat it like it a date, and therefore sort it like a date.

-mobailey

 

[lost]

Also, PLSD have a "Date fields to_char" option. Check it out.

 

[William Robertson]

What is the datatype of LIEF_DAT? If it's a string, it's correctly sorted. I've never known PL/SQL Developer to have a problem sorting actual dates.

 

[Baila_me]

Lief_Dat is datatype Timestamp

 

[Baila_me]

now i could more Details. if i sort the timestamp with order by then it is sorted correctly. But if i sort with the button at the columns then the PL/SQL Developer sorted wrong. What can i do? The funtion date to_char is disabled

 

[Baila_me]

anyone a idea?

 

[jhughes]

Create a scenario that can reproduced by others. By that I mean, create a new table, insert some rows (say 4), write the SQL statement that reproduces the sorting issues you are seeing and share that with everyone here. This means you will have a CREATE TABLE statement, x number of INSERT statements, and a SELECT statement to share. Also include your version of PSD. From there, people can confirm whether they can reproduce the issue or not. So far you've provided the result, but nothing we can use to actually see or reproduce what you are doing.

 

[koja]

When sorting using the sort buttons in the sql window results:
In the configuration panel it is possible to set a date format for the date(user interface - nls_options - date). When set to YY-MM-DD a date column can be sorted using the sort buttons in the sql window results. For timestamp fields this format can not be set and sorting using the sort buttons sorts alphabetically as far as I can see. I have the number fields to char and date fields to char options deselected.

Sorting on date, time, timestamp, guid and number etc can best be done using the order by in the select of course.( btw when sorting on guid, make sure the guid is generated from the same source(before insert row trigger fi), else one may have unexpected results. Don't know yet how a database migration will effect the guid sort order.