Sort by time in text string field (hh:mm:am/pm format)

[00steve]

Hi,

I have a group of records that I need to sort by time field. The time
field is a text string (should've been a date/time - I know -doh!) I
was wondering if anyone knew how to write a query to sort in Ascending
order with the earliest appearing first.

The times are stored as follows:

10:30pm
11:30pm

etc.

Many, many thanks to anyone who can help with this one. Cheers.

[Aggro]

00steve wrote:

I have a group of records that I need to sort by time field. The time
field is a text string (should've been a date/time - I know -doh!)

Is it impossible to convert the string field to be time field? This
should be possible with few queries on database, but does the
application logic allow it, or if not, can the logic be changed or not?

And do you have leading zeros or not, for example: "5:08pm" or "05:08pm"?

[Felix Geerinckx]

29****@tay.ac.uk (00steve) wrote in message news:<df**************************@posting.google. com>...

I have a group of records that I need to sort by time field. The time
field is a text string (should've been a date/time - I know -doh!) I
was wondering if anyone knew how to write a query to sort in Ascending
order with the earliest appearing first.

The times are stored as follows:

10:30pm
11:30pm

Try

ORDER BY
IF(INSTR(time_field, 'pm'), 12*60*60, 0) + TIME_TO_SEC(time_field) ASC

--
felix