Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Unanswered: 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?

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    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
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Jun 2003
    Posts
    34
    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.

  4. #4
    Join Date
    Sep 2003
    Posts
    6
    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!

  5. #5
    Join Date
    Sep 2003
    Posts
    6
    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!!!!!

  6. #6
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76

    ....

    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

  7. #7
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    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.
    Edwin van Hattem
    OCP DBA / System analyst

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you still have a sorting problem, which you will discover in january


  9. #9
    Join Date
    Sep 2003
    Posts
    6
    Originally posted by r937
    you still have a sorting problem, which you will discover in january

    what do you mean? what will happen in jan?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    *********************************
    *********************************
    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


    rudy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •