Let’s say you want to order these times below. of course a simple “order by” will not do it, what you need is the query given below
1:00 PM, 1:30 PM, 10:00 AM, 10:30 AM, 11:00 AM,
11:30 AM, 12:00 PM, 12:30 PM, 2:00 PM, 2:30 PM,
3:00 PM, 3:30 PM, 4:00 PM, 4:30 PM, 5:00 PM,
5:30 PM, 6:00 PM, 6:30 PM, 7:00 PM, 9:00 AM, 9:30 AM
SELECT Time FROM tablename ORDER BY CONVERT(SMALLDATETIME, '19990102 ' + Time )
Technically what you are doing is appending the time to a fixed date and let SQL handle the sorting.