I want to fetch data from a MySQL database's table and order the selected data by date.
Which data format is ordered most precisely by MySQL? I also want the date format to be unique. Please express the format just like PHP's one, example: h:i:s.
Sorry but I wasn't talking about how to use 'Order By' clause in SQL.
My question was how to use the 'Order By' clause to work with date/time 100% accurately. Normally, it doesn't work properly as MySQL cannot recognize which one is month, hour, day, etc
How do you store your dates?
Using a unix timestamp or unix datetime (yyyy-mm-dd H:i:s) should prove to be consistent. I can't see why there should be a problem - unless you're storing dates in a non-standard format.
I understand now - I don't have an answer for you with regard to your format but why don't you use the mysql date formats available as they will then store all your date/time information correctly so you will be able to ORDER BY them and then just re-format your dates when you echo out the dates on the front end?
It makes no sense to me to store your dates and times in a custom format when you don't need to and subsequently cause problems in ordering as you are having.
Another solution would be to use MySQL's STR_TO_DATE() function:
SELECT DATE_FORMAT(STR_TO_DATE(datefield, '%M %d, %Y %h:%i %p'),'%Y-%m-%d %H:%i:%s') AS fdate FROM datetable ORDER BY fdate
The date_format is probably overkill though. This could be used to UPDATE your weird dates to standard ones: