MySQL order by date (M D, Y, H:M:S am\pm)
I've been messing around with this for hours trying different ways of ordering by date. I wish there was an strtotime(field) that would do what I need.
I've tried messing around with UNIX_TIMESTAMP, STR_TO_DATE and I just can't figure this out.
I'm trying to put these in order given below:
June 13, 2012, 4:45:28 pm May 31, 2012, 10:41 am June 12, 2012, 6:40:34 pm June 06, 2012, 3:05:22 am June 10, 2012, 7:59:31 pm
I've tried a bunch of different approaches, this is the latest one I've been working on...
SELECT name,lastonline FROM `users` ORDER BY STR_TO_DATE(UPPER(lastonline), '%M %d,%Y, %H:%i:%s') DESC
It seems as though this is as close as I can get and it doesn't take into account "AM" or "PM". I've tried adding the %p on the end, but for some reason it's as though the UPPER isn't doing anything at all. I've also tried using %r.
The MySQL docs say, that a construct of 12-Hour time and AM/PM should be parsed as %r in one piece, not as %H:%i:%s %p.
While I consider this to be a misfeature, it definitly works.
you may use HH24 instead to get a full range of hours.