Hi guys, just thought I’d add I needed to do this (sort posts by event date rather than post date) for a project I’m working on, and this is my solution:
$querystr = "
SELECT wposts.*
FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
WHERE wposts.ID = wpostmeta.post_id
AND wpostmeta.meta_key = 'date'
AND wposts.post_status = 'publish'
AND wposts.post_type = 'post'
ORDER BY STR_TO_DATE(wpostmeta.meta_value, '%m/%d/%Y') ASC
";
$pageposts = $wpdb->get_results($querystr, OBJECT);
The date there must be inputted in the American format ie 21/02/2009 for it to work, hopefully my client will be ok with that!
The reason for using that date format is for later handling it in PHP, of course you could store the date in YYYY/MM/DD format and thus the STR_TO_DATE clause would be redundant.
