20/20 hindsight - date problem

[AndyB]

A problem with legacy data (lots of it, unfortunately) in a MySQL database that needs to be sorted by date except that the 'date' field is actually just a varchar field generated thus:

[code]$dated = gmdate("h:i a"). "[GMT] on ". gmdate("F d Y")[/code]

I'm hoping that the query can be constructed with 'magic' that someone can suggest will work with that date data and php4.4.2/MySQL 4.1.21-standard

[MarioRossi]

tried the strtotime function on it? If its all in the same format you should be OK, just add another db field with a more universal format and rewrite back to the DB

[url=http://uk2.php.net/strtotime]http://uk2.php.net/strtotime[/url]

[AndyB]

By way of clarification, an example of the 'date' field is [b]12:05 pm [GMT] on September 06 2006[/b].

I don't want to re-write the 'real' date back to the database, I *only* want an SQL query that can sort the data by yyyy-mm-dd when the database field contains a string like the example above.

[shoz]

[code]
SELECT
*
FROM
tablename
ORDER BY
STR_TO_DATE(SUBSTRING(date_column, LOCATE('on', date_column) + 3), '%M %d %Y')
[/code]

You should be able to set the substring start position to a hardcoded 19 but I'm not sure.
[code]
STR_TO_DATE(SUBSTRING(date_column, 19), '%M %d %Y')
[/code]

It may be faster to sort this in PHP with usort.

[AndyB]

The posted query did everything I had hoped for. Thanks, shoz.