help date will not order by desc

Why is october at the bottom of the list?

sql code:
SELECT userInfo.*, marks.*, marks.datefield FROM userInfo INNER JOIN marks ON userInfo.userID = marks.userID ORDER BY marks.datefield desc;

this is what i get:
9/30/2003
9/23/2003
9/19/2003
9/1/2003
8/25/2003
8/21/2003
8/19/2003
8/19/2003
10/16/2003
10/1/2003

Why is october at the bottom of the list?hi,

alphabeticly the 9 is higher then the 1. Like any other tool/rdbms oracle sort character by character and 9 is higher than 1. U have to format the dates before sorting to make sure that oracle compares 10 to 9 instead of 1 to 9. Do so by formatting the date like:

select to_char(sysdate,'MM/DD/YYYY') from dual.

Then u get '09/12/2003'. Then compare it to the other dates and the sorting will be good.

Hope this helps|||Originally posted by evanhattem
hi,

alphabeticly the 9 is higher then the 1. Like any other tool/rdbms oracle sort character by character and 9 is higher than 1. U have to format the dates before sorting to make sure that oracle compares 10 to 9 instead of 1 to 9. Do so by formatting the date like:

select to_char(sysdate,'MM/DD/YYYY') from dual.

Then u get '09/12/2003'. Then compare it to the other dates and the sorting will be good.

Hope this helps
Am bit confused here I tried reproducing this problem but never got what is shown here. I tried the table with date field and also varchar field both ways the DESC worked.|||Originally posted by evanhattem
hi,

alphabeticly the 9 is higher then the 1. Like any other tool/rdbms oracle sort character by character and 9 is higher than 1. U have to format the dates before sorting to make sure that oracle compares 10 to 9 instead of 1 to 9. Do so by formatting the date like:

select to_char(sysdate,'MM/DD/YYYY') from dual.

Then u get '09/12/2003'. Then compare it to the other dates and the sorting will be good.

Hope this helps

select to_char(sysdate,'MM/DD/YYYY') from dual.
could you describe what to_char stand for? or use my code to make it work? i am at a loss. thanks for replying!|||Originally posted by wadecarlson
select to_char(sysdate,'MM/DD/YYYY') from dual.
could you describe what to_char stand for? or use my code to make it work? i am at a loss. thanks for replying!

i just changed the data type in access to date/time and this seemed to fix the glitch.

thx all!!!!!|||basically what he is saying is to format the date differently.

instead of viewing 9/1/2003 change the format to 09/1/2003, with placing the 09 instead of 9 it will order properly.

just format the date column|||Originally posted by wadecarlson
select to_char(sysdate,'MM/DD/YYYY') from dual.
could you describe what to_char stand for? or use my code to make it work? i am at a loss. thanks for replying!

Oracle knows only one format for the internal storing of a date. But we can show it differently. Internally it would be like mm/dd/yyyy, but we can show it like DD-Month-YY. To do so, we have to convert the date to a character string, because Oracle knows only one format of the date internally. TO_CHAR changes a date or a number value to a character, allowing us to display it in different format.

Good thing u have got it working.

Greetz.|||you still have a sorting problem, which you will discover in january

:cool:|||Originally posted by r937
you still have a sorting problem, which you will discover in january

:cool:

what do you mean? what will happen in jan?|||*********************************
*********************************
W00H00!!! my 1000th dbforums posting!
*********************************
*********************************

in january, if you are still using TO_CHAR to format and sort your dates, you will find that 01/01/2004 comes ahead of 12/31/2003 because you are sorting them as character strings

rudysql