#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Nov 2009
    Posts
    49
    Rep Power
    8

    Order by Time stored as varchar


    I have a varchar field filled with times in a format like '14:33:45.78'. I would like to query this field and order the results based on Time.

    Not sure if this is possible but any help would be greatly appreciated.

    Geno
  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,626
    Rep Power
    653
    Varchar field probably means the values are treated as text strings not Date/Time. If this is case, sorting possible as long as all strings have the same length. Might require place holder zeros. Example 03:10:01.17, not 3:10:1.17. Will have to control this during data entry, possibly with masking.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm
  4. #3
  5. Senior Water Wizard
    ASP Mastermind (5000+ posts)

    Join Date
    Aug 2004
    Location
    Cape Town, RSA
    Posts
    10,233
    Rep Power
    862
    you can use a cast statement, if you cast the time it will automatically add a default date, but that wont influence your results
    Code:
    select *
    from table1
    order by cast(timeField as datetime) ASC
    hope this helps

    Comments on this post

    • June7 agrees : Then don't worry about place holders, just have to remember to use cast everytime calc this data.
    Look! Its a ShemZilla


  6. #4
  7. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    Sep 2009
    Posts
    39
    Rep Power
    14

    Re: Order by Time stored as Varchar


    Hi,

    I hope that the below query will be useful for u

    Code:
    Select Time from tablename order by cast(Time as datetime) asc
    Note:

    If the field in varchar convert the Varchar field into datetime and order the date time record

    Thanks & Regards
    Sakthimeenakshi.S




    Originally Posted by Geno45
    I have a varchar field filled with times in a format like '14:33:45.78'. I would like to query this field and order the results based on Time.

    Not sure if this is possible but any help would be greatly appreciated.

    Geno

Similar Threads

  1. Opinion: School's out: Time to order new Macs? (Macworld.com)
    By RSS_News_User in forum Technology News
    Replies: 0
    Last Post: June 29th, 2006, 01:08 PM
  2. "Order By" madness in stored proc
    By Don Silver in forum Microsoft SQL Server
    Replies: 3
    Last Post: May 4th, 2005, 04:32 PM
  3. Time and Date
    By wdale in forum ASP Development
    Replies: 3
    Last Post: August 18th, 2004, 08:14 AM
  4. Time?
    By wdale in forum ASP Development
    Replies: 1
    Last Post: August 14th, 2004, 02:31 PM
  5. Web serverístatistical analysis
    By cso in forum ASP Development
    Replies: 0
    Last Post: April 19th, 2003, 11:35 AM

IMN logo majestic logo threadwatch logo seochat tools logo