Need help sorting time values

[parksch2]

I have been trying and trying to properly sort records through a mySQL
query. I have hour, minute and AM/PM values stored separately in a
database as varchars. I'm trying to concat those, cast them to a time
value and sort them. I have found that it sorts some properly but other
it does not.

The following query:
CAST(CONCAT(hour, ':', minute, ' ', ampm) AS TIME) AS myTime
ORDER BY myTime

Sorts some results like this:
10:30 PM
8:30 PM
9:30 PM

But other are perfect:
10:00 AM
11:00 AM
12:00 PM
1:00 PM
2:00 PM
3:00 PM
4:00 PM
5:00 PM

These exact results we part of the same recordset. Can anyone help me
with this? It would be greatly, greatly appreciated.

Thanks in advance!

[jerry gitomer]

pa******@hotmail.com wrote:

I have been trying and trying to properly sort records through a mySQL
query. I have hour, minute and AM/PM values stored separately in a
database as varchars. I'm trying to concat those, cast them to a time
value and sort them. I have found that it sorts some properly but other
it does not.

The following query:
CAST(CONCAT(hour, ':', minute, ' ', ampm) AS TIME) AS myTime
ORDER BY myTime

Sorts some results like this:
10:30 PM
8:30 PM
9:30 PM

But other are perfect:
10:00 AM
11:00 AM
12:00 PM
1:00 PM
2:00 PM
3:00 PM
4:00 PM
5:00 PM

These exact results we part of the same recordset. Can anyone help me
with this? It would be greatly, greatly appreciated.

Thanks in advance!

Your problem is that either your data is being stored without
leading blanks or that your code is eliminating them. You can
determine if the data is stored with/without leading blanks
using a simple select that encloses the data in quotation marks.

If the output looks like "8" your problem is that data is being
stored without leading blanks.

If it looks like " 8" your code is wiping out the leading blanks.

HTH
Jerry

[parksch2]

Thanks for the response Jerry. I'm a bit confused. Do I want the
spaces? Shouldn't the cast take care of those? Should I be trimming the
values?

[Jonathan]

pa******@hotmail.com wrote:

I have been trying and trying to properly sort records through a mySQL
query. I have hour, minute and AM/PM values stored separately in a
database as varchars. I'm trying to concat those, cast them to a time
value and sort them. I have found that it sorts some properly but other
it does not.

The following query:
CAST(CONCAT(hour, ':', minute, ' ', ampm) AS TIME) AS myTime
ORDER BY myTime

Sorts some results like this:
10:30 PM
8:30 PM
9:30 PM

But other are perfect:
10:00 AM
11:00 AM
12:00 PM
1:00 PM
2:00 PM
3:00 PM
4:00 PM
5:00 PM

These exact results we part of the same recordset. Can anyone help me
with this? It would be greatly, greatly appreciated.

Thanks in advance!

If you use the date_format routine for the generated time and specify is
like so it should work as well I think:

DATE_FORMAT(CAST(CONCAT(hour, ':', minute, ' ', ampm) AS TIME), '%r') as
mytime ORDER BY myTime

This way it should generate date/time in the hh:mm:ss format followed by
AM or PM according to
http://dev.mysql.com/doc/mysql/en/da...functions.html

Jonathan