zebe Posted July 12, 2006 Share Posted July 12, 2006 Hi, I am writing a script that needs to sort by a field which contains a time value. I need to sort it in order of earlies to latest. The field type is a varchar. People have been putting in am/pm characters in this field as well and when I go to sort it comes out something like this:8:45am8:45am8:45am9:15am9:30am9:30am9am9amIn my query I have the following: [code]ORDER BY REPLACE(bus_leaves_uri, ':', '')" [/code]I was trying to take out the ":" thinking that was the problem with the ordering. Does anyone know a better mysql function that will actaully sort this type of data correctly?Thanks for any help! Quote Link to comment Share on other sites More sharing options...
jworisek Posted July 12, 2006 Share Posted July 12, 2006 decide on a new format and adjust the existing data for that... I would say either use military time (1pm being 13:00) or having a seperate column to check am or pm. Then you also want to check the input to make sure that people only enter valid times. If its going to be a headache, make a dropdown for the hours then a dropdown for the minutes and combine them before submitting. Quote Link to comment Share on other sites More sharing options...
fenway Posted July 12, 2006 Share Posted July 12, 2006 The real solution is to use a proper TIME column, and then the sorting is free. You can always format it nicely on the way out. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.