SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jan 2005
    Location
    Maine
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL- order by date

    When I do an order by date I get the following:

    03-DEC-04
    08-DEC-04
    01-JAN-05
    11-NOV-04

    What I want is:

    11-NOV-04
    03-DEC-04
    08-DEC-04
    01-JAN-05

    Does anyone know how to do this?

    Thanks

  2. #2
    Drupaler bronze trophy greg.harvey's Avatar
    Join Date
    Jul 2002
    Location
    London, UK
    Posts
    3,258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Please read:

    http://www.cl.cam.ac.uk/~mgk25/iso-time.html

    As ORDER BY Date should behave in the way you expect, I suspect your dates are corrupted - usually happens as a result of not using the International Date Time Format as detailed above - I am only guessing, but it's good practice anyway so I would read it and use the format if I were you...


  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    i'm guessing it's simpler than corrupted data

    the date field is probably VARCHAR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2003
    Location
    va
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Date field should not be VARCHAR.
    You should use DATETIME. Otherwise, you are going
    to have to do all kind of conversions. Beside the conversion problem,
    you are going to have a difficult time with sorting and
    comparing the date fields.
    Define the date field as DATETIME, let
    the client application retrieves the date field and does the necessary
    conversion.
    http://www.GeekGig.com - Resources for the Geeks!


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
  •