Jump to content

Archived

This topic is now archived and is closed to further replies.

AndyB

20/20 hindsight - date problem

Recommended Posts

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

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
[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.

Share this post


Link to post
Share on other sites
The posted query did everything I had hoped for. Thanks, shoz.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.