Sorting Time Strings

OK, so I’m working on a MySQL database of tour reservations that has time values stored as strings in an AM/PM time format. The hour portion of the value does not contain a leading 0, so “6:00 AM”, “11:35 AM”, “4:45 PM” are all possible values. I’ve written a query to pull a sorted list of the unique tour times from the database that looks something like this:

SELECT DISTINCT reservation_time,
FROM reservations
WHERE DATEDIFF(reservation_date, '2006-12-11')=0
AND is_canceled=0
ORDER BY SUBSTRING(reservation_time, -2),
CAST(SUBSTRING_INDEX(reservation_time,':',1 ) AS UNSIGNED),
CAST(SUBSTRING(reservation_time,-5, 2) AS UNSIGNED);

My problem is what follows the ORDER BY statement. I’m sorting the list of time values 3 times: first by the last 2 digits of the time (AM or PM), then by the hour, then by the minutes. I can’t use SUBSTRING( reservation_time, 2 ) to get the hour value because some have one digit and some have 2. This also means I have to get the minutes by counting from the right side instead of from the left. My question, to all my SQL fluent friends out there is whether or not this is the best way to get this data. Ordering the data 3 times seems a bit inefficient, but then I’m no SQL wizard.