#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Dubai, UAE
    Posts
    449
    Rep Power
    15

    How to get dates in database to sort properly.


    I am trying to have a list in my table output in the order they are loaded by date,

    here is my code:
    Code:
    <!----------- Query DB -------------------->
    <cfquery name="Resumes" datasource="Students">
    SELECT * FROM Resume ORDER BY PostDate DESC
    </cfquery>
    But the dates that are output are all messed up, and not in order. Is there something I am missing?
  2. #2
  3. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    Is postDate a date field or a string field?
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Dubai, UAE
    Posts
    449
    Rep Power
    15
    It's a date field.
  6. #4
  7. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    hmm...then I'm not sure. If the date field is holding a valid date value, using that field to order by should order the results by the date. How is the the ordering messed up?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Dubai, UAE
    Posts
    449
    Rep Power
    15
    Output:

    11/28/03
    3/7/04
    3/5/04
    3/29/04
    3/24/04
    3/23/04
    3/2/04
    3/18/04
    3/12/04
    3/1/04
    1/27/04
    1/15/04
    2/9/04
    12/16/03
  10. #6
  11. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    I'm not sure what to tell you on this one. It seems to me there MUST be some problem in the way this has been stored in the database. What database are you using? What is the exact data type of the postdate field? Is it a timestamp field? date type? datetime?

    I can run the same type of query on one of my tables:

    select create_date from cm_task
    order by create_date desc

    and the results come back in exactly the right order. The create_date field is of Oracle type "date".
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Dubai, UAE
    Posts
    449
    Rep Power
    15
    It's an access database, and I think I figured it out.

    The PostDate in the access database wasn't actually set to a Date Field, it was set at Memo .

    All good now.
  14. #8
  15. No Profile Picture
    Moderator

    Join Date
    Jun 2002
    Location
    Raleigh, NC
    Posts
    5,264
    Rep Power
    968
    Originally Posted by kiteless
    Is postDate a date field or a string field?
    That's what I was getting at with this question. Glad you got straightened out.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Location
    Dubai, UAE
    Posts
    449
    Rep Power
    15
    Originally Posted by kiteless
    That's what I was getting at with this question. Glad you got straightened out.
    Yea, I must have changed it once without knowing....

IMN logo majestic logo threadwatch logo seochat tools logo