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! Link to comment https://forums.phpfreaks.com/topic/14398-mysql-sorting-question/ 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. Link to comment https://forums.phpfreaks.com/topic/14398-mysql-sorting-question/#findComment-56862 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. Link to comment https://forums.phpfreaks.com/topic/14398-mysql-sorting-question/#findComment-56878 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.