By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,564 Members | 810 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,564 IT Pros & Developers. It's quick & easy.

Need help sorting time values

P: n/a
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!

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jul 23 '05 #2

P: n/a
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?

Jul 23 '05 #3

P: n/a
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
Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.