I'm trying to build a query in order to sort some posts by a date custom field, where the dates are formatted dd/mm/yyyy.

It works by querying the database directly with get_results like in this example :

$querystr = "
    SELECT *
    FROM wp_posts, wp_postmeta
    WHERE wp_posts.ID = wp_postmeta.post_id
    AND wp_postmeta.meta_key = 'my_cutom_date'
    AND wp_posts.post_status = 'publish'
    AND wp_posts.post_type = 'post'
    ORDER BY STR_TO_DATE(wp_postmeta.meta_value, '%d/%m/%Y') ASC

But, I would like to transpose this behavior to a WP_Query, using a posts_where filter. I got as far as this :

$args = array(
    'post_type' => 'post',
    'post_status' => 'publish',
    'posts_per_page' => 10,
    'meta_key' => 'my_custom_date'

function filter_orderby( $orderby = '' ) {  
    global $wpdb;
    $orderby .= " ORDER BY STR_TO_DATE($wpdb->postmeta.meta_value, '%d/%m/%Y') ASC ";
    return $orderby;

add_filter( 'posts_orderby', 'filter_orderby' );

But it doesn't seem to return anything. what am I missing ?

  • 1
    Do you have the resulting SQL query that gets executed? Filter on posts_request and var_dump() what gets passed, or install the Debug Bar with SAVEQUERIES enabled. I think the problem is that you don't have to specify ORDER BY yourself.
    – Jan Fabry
    Jun 1, 2011 at 8:42
  • @Jan, The Debug bar is great, thanks a lot for that. And yes there is an error in the SQL. The thing is there are 2 ORDER statements, one default (I guess) and the one I add in my filter : ORDER BY cs_posts.post_date DESC ORDER BY STR_TO_DATE(cs_postmeta.meta_value, '%d/%m/%Y') ASC How can I remove the ORDER BY cs_posts.post_date DESC ?
    – mike23
    Jun 1, 2011 at 11:14
  • @Jan, if I remove ORDER BY the result is the same, I get an error. I also tried with AND, for ex :ORDER BY cs_posts.post_date DESC AND STR_TO_DATE(cs_posts.meta_value, '%d/%m/%Y'), but with no chance. Now I'm wondering, the filter lets you only add things to the query ? It's impossible to overwrite the defaults ?
    – mike23
    Jun 1, 2011 at 13:08
  • @myself, of course it is, by using "=" instead of ".=", as usual in php, duh.
    – mike23
    Jun 1, 2011 at 13:20

1 Answer 1


The filter on posts_orderby should return a string that does not begin with ORDER BY, WordPress will add that itself.

By default WordPress will filter by post_date. If you don't want that, you should overwrite the order clause, not append to it. So your filter should look like this:

function filter_orderby( $orderby ) {  
    global $wpdb;
    return " STR_TO_DATE({$wpdb->postmeta}.meta_value, '%d/%m/%Y') ASC ";

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.