Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Exclamation Unanswered: Wildcards in SQL????

    Hi,

    I’ve inherited a database which uses a single text field to store a date and time. The format is:

    Dd/mm/yyyy hh:mm:ss
    Eg
    12/01/2005 23:13:01

    I need a SQL query that returns fields sorted by date and time most recent first– but ordering as a string returns the wrong order: eg:

    01/01/2005 12:00:01
    02/04/2004 12:00:01
    03/02/2005 12:00:00

    So….. I need an SQL string that returns sorts by the year component, then month component, then day part then time component of the field. Any ideas how this is done in SQL? Any guidance appreciated.

    John

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use the MID function four times in your ORDER BY

    alternatively, convert the column to Date/Time datatype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by r937
    alternatively, convert the column to Date/Time datatype
    The 'best' solution is store the data as it shouild be as Rudy suggests if its a date time value then store it as a date time value not a string. That way round you can easily analyse the data by any relevant element (eg day, month, year, hour, weekday etc....

    If you are going to change the datatype to date/time then make sure you do a backup first!

  4. #4
    Join Date
    Feb 2005
    Posts
    2

    thanks, but

    I agree, the implementation should use proper time field type but the implementation (which is live !!!) uses strings. I have to work on the data structures I have

  5. #5
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Have a look at CDate in the help files. This function is used to convert a string to a date.

Posting Permissions

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