MYSQL order by time am/pm

Question

I have a row in a table which holds the time of day in am/pm format e.g.

timeField
---------
9.00am
10.00pm
7.00am 
etc...

Is there a way in mysql to order these values?

Answer 1

You can do this by using STR_TO_DATE function in MySQL:

SELECT STR_TO_DATE('10.00pm','%h.%i%p');

try this:

SELECT * 
FROM table_name 
ORDER BY STR_TO_DATE(timeField,'%h.%i%p');

Example: SQLFiddle

Answer 2

In order to order it you need to format the date field fist.

try this

SELECT COl1, COl2, DATE_FORMAT(yourdate, '%k:%i:%s') AS mydate
FROM your_table
ORDER BY mydate 

Enjoy