meta value has a date, how to use order by?

[indyank]

I have a custom field called event_date (meta_key). Now value for this custom field are stored in "dd month year" format. Eg : 01 July 2013.

How do I sort the wordpress query by this custom field?

I have tried this in pre_get_posts filter fucntion, but it doesn't seem to work..

$query->set('meta_key' , 'event_date');
$query->set('orderby' , 'STR_TO_DATE( meta_value, "%d %M %Y" )');

I have found a few answers on the web where storing dates in yyyy/mm/dd format has worked with this.

$query->set('meta_key' , 'event_date');
$query->set('orderby' , 'meta_value');

But in my case, the date is stored in a different format. meta_value is a column of longtext data type.

Can someone pls. help.

[lorax]

What version of MySQL and PHP are you running?

[indyank]

PHP - 5.4.9
Mysql - 5.5.31

But I think it is a wordpress limitation as I am trying to alter the main query in wordpress.

[lorax]

Off the top of my head I agree with you. Have you tried creating your own query to see if you can manipulate the result set?

[indyank]

Unfortunately, crating my own query wasn't an option. So the workaround was to somehow convert the existing meta-values for 'event_date' meta_keys into the ISO format "yyyy-mm-dd" and then this worked.

$query->set('meta_key' , 'event_date');
$query->set('orderby' , 'meta_value');

Thanks lorax.