SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    Jersey
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    order by problem with datetime

    I have datetime information for news posts stored in my database table and obviously i want the latest post to display first; so logically I order the SQL selection in descending order by the datetime column. However...

    I only noticed this problem when the month moved into february. If i order the results in DESCENDING order by the datetime column i get the following results.

    30/01/2006 18:42
    23/01/2006 19:32
    18/01/2006 02:47
    02/02/2006 15:37

    Obviously there is a problem with this - even though the latest post is from 2nd February, the post from the 30th of January comes first because the day is the 30th - they're ordered descendingly by the days.

    Ordering them in ascending order gives the following results.

    02/02/2006 15:37
    18/01/2006 02:47
    23/01/2006 19:32
    30/01/2006 18:42

    As you can see i get my latest post first, but it's still ordered by the days. This isn't any good if i make a news entry on the 3rd March (03/03)

    How can i order them correctly? I've never had this problem before, but i've always formatted datestrings from within my application before inserting and stored them as type varchar. Currently these are stored as datetime objects and the formatting is done when the data is read.

    Is there anyway i can order them correctly without storing them as varchars, I need to perform date formatting at runtime rather than when the record is created.
    Matt Daly

  2. #2
    SitePoint Enthusiast niardica's Avatar
    Join Date
    Sep 2002
    Location
    CA
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you sure the column datatype is datetime and not varchar or some other string type?

    I just ran a few simple tests to show you, but if they are datetime it should definitely be ordering correctly..

    Code:
    create table DateTest (test_date datetime NOT NULL)
     
     insert DateTest values (convert(datetime, '01/18/2006 02:47:00'))
     insert DateTest values (convert(datetime, '01/23/2006 19:32:00'))
     insert DateTest values (convert(datetime, '02/02/2006 15:37:00'))
     insert DateTest values (convert(datetime, '01/30/2006 18:42:00'))
     
     select test_date as desc_test
     from DateTest
     order by test_date desc
     
     select test_date as asc_test
     from DateTest
     order by test_date asc
     
     drop table DateTest
    --- RESULTS ----
    Code:
     
     desc_test											  
     ------------------------------------------------------ 
     2006-02-02 15:37:00.000
     2006-01-30 18:42:00.000
     2006-01-23 19:32:00.000
     2006-01-18 02:47:00.000
     
     (4 row(s) affected)
     
     asc_test											   
     ------------------------------------------------------ 
     2006-01-18 02:47:00.000
     2006-01-23 19:32:00.000
     2006-01-30 18:42:00.000
     2006-02-02 15:37:00.000

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Location
    Jersey
    Posts
    175
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah yes you were correct. when making the post i stupidly only checked my stored procedure where i declared the variable to be entered as datetime, but sure enough i still have the table column set as a varchar.

    thanks for the reply and sorry for the time.
    Matt Daly

  4. #4
    SitePoint Enthusiast niardica's Avatar
    Join Date
    Sep 2002
    Location
    CA
    Posts
    34
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem.. i figured it would be a simple fix


Bookmarks

Posting Permissions

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