Results 1 to 5 of 5

Thread: sorting problem

  1. #1
    Join Date
    Dec 2003
    Location
    san jose
    Posts
    3

    Unanswered: sorting problem

    Hello,

    I have an appointment table that stores the appt time as varchar. For example, 1:00 PM or 9:00 AM. When I say "ORDER BY Appointment.Time", it puts 1:00 PM before 9:00 AM. How do I make it put 9:00 before instead? Could you please help me out.

    Please note, I store time as varchar because it is much more convenient for me through out the application that way.

    Thank you very much.

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    SELECT * FROM Appointment ORDER BY CAST([Time] AS Datetime) ASC

  3. #3
    Join Date
    Dec 2003
    Location
    san jose
    Posts
    3
    Originally posted by gyuan
    SELECT * FROM Appointment ORDER BY CAST([Time] AS Datetime) ASC

    Thank you very much. It works like a charm.

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    This is the 3rd post in a couple of days with the same issue. When you are working with date/time values you should store them as datetime datatype within sql server. The problem is if you order by a varchar value it will sort them according to their ascii values - so 1 comes before 9 which is why your order did not function as you expected.

    Based on your appointment requirements, the datetime data type should work fine for you.

  5. #5
    Join Date
    Nov 2003
    Posts
    94
    Originally posted by rnealejr
    This is the 3rd post in a couple of days with the same issue. When you are working with date/time values you should store them as datetime datatype within sql server. The problem is if you order by a varchar value it will sort them according to their ascii values - so 1 comes before 9 which is why your order did not function as you expected.

    Based on your appointment requirements, the datetime data type should work fine for you.
    Absolutely, and what's worse is that

    ORDER BY CAST([Time] AS Datetime)

    Causes the entire result set to be spooled in tempdb, every row in column [Time] coverted to a datetime, and then sorted, every time the query is run.

Posting Permissions

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