How to Sort by Date When Using d-m-Y Format

You need to extend the MySQL-Query directly, as you have to use the MySQL-Function STR_TO_DATE in order to not mess with the pagination etc.

You should be able to achieve this with a change in your pre_get_posts like this:

add_action( 'pre_get_posts', 'courses_columns_orderby' );

function courses_columns_orderby( $query ) {
    if( ! is_admin() )
    return;
    $orderby = $query->get( 'orderby');
    switch( $orderby ){
        case 'online_start': 
            add_filter( 'posts_join', 'do_order_by_custom_date_field_join' );
            add_filter( 'posts_orderby', 'do_order_by_custom_date_field_orderby',10,2 );
            break;
        default: break;
    }
}

Now we join our meta_table manually so that we can define which alias it gets:

function do_order_by_custom_date_field_join($join){
    global $wpdb;
    $join.="LEFT JOIN $wpdb->postmeta my_order_meta ON (($wpdb->posts.ID = my_order_meta.post_id) AND (my_order_meta.meta_key = 'online_start')) ";
    //as we don't want our join to be used more than once on this page, we remove the filter
    remove_filter( 'posts_join', 'do_order_by_custom_date_field_join' );
    return $join;
}

At last, we change our ordering to the date, but with the STR_TO_DATE function:

function do_order_by_custom_date_field_orderby($orderby, $wp_query){
    $orderby = "STR_TO_DATE(my_order_meta.meta_value,'%d-%m-%Y') ".$wp_query->get('order');
    remove_filter('posts_orderby', 'do_order_by_custom_date_field_orderby');
    return $orderby;
}

Now, the orderby should work as intended (didn’t test it tho)

Happy Coding.

Leave a Comment