0

I am using the code below to sort code as per meta_value. But in this specific Column I have dates displayed for example: 20 April 2018 or 01 June 2018

The sorting works sort of but now when I order the columns it displays it like 29 April 2018, 29 May 2018, 29 June 2018, 28 April 2018, 28 May 2018, 28 June 2018...and so and so on...

Is there a way to sort that column in a proper date format like 29 April 2018, 28 April 2018, 27 April 2018 etc....???

Code I am using as follows:

add_filter( 'manage_edit-post_sortable_columns', 'closing_sortable_columns' );

function closing_sortable_columns( $columns ) {

    $columns['closing'] = 'closing';

    return $columns;
}

add_action( 'pre_get_posts', 'closing_column_orderby' );  
function closing_column_orderby( $query ) {  
    if( ! is_admin() )  
        return;  

    $orderby = $query->get( 'orderby');  

    if( 'closing' == $orderby ) {  
        $query->set('meta_key','closing');  
        $query->set('orderby','meta_value_num');  
    }  
} 

I tried to change meta_value_num to asc but this just mixes up everything and makes the overview even worse.

1

By default meta values will be treated as strings, leading to the result that you get. As you can see from the codex on wp_query you can force a comparison with different formats by defining a meta_type. Like this:

    $query->set('meta_type','DATETIME');  
  • Thanks but this does not change anything. I think I am using the wrong format in my custom field because everything is typed in manually. Could this be the reason? – mad2kx May 27 '18 at 8:49
  • Ah, yes, certainly. I was assuming you were using a date format. After all, there is a special html form input field for that. @cybmeta 's answer covers conversion as well. But if everything is typed in manually you might run into problems with typos. – cjbj May 27 '18 at 9:09
  • Alright thanks. I am marking your response as helpful even it did not help me with my problem. But now changing 1300 posts with correct formating would probably be consuming lots of time – mad2kx May 27 '18 at 9:15
  • You might try to automate that by running a loop on all posts and the php function strtotime. Backup your database before trying... – cjbj May 27 '18 at 9:20
  • 1
    @mad2kx you should mark the answer that actually fix/answer your problem/question. Accepting an answer that (quoting you) "didn't help with your problem" may be confusing for other users with the same problem, and this site is intended to be helpfull to many people as possible. – cybmeta May 27 '18 at 9:59
0

You need dates to be stored in a sortable format. For example, MySQL date format (which is the date format WordPress uses in database for dates by default):

// Convert date to MySQL date format before it is stored in database
$date = date('Y-m-d', 'the_date_value' );

Then you can sort by that date field:

add_action( 'pre_get_posts', 'closing_column_orderby' );  
function closing_column_orderby( $query ) {  
    if( ! is_admin() )  
        return;  

    $orderby = $query->get( 'orderby');  

    if( 'closing' == $orderby ) {  
        $query->set('meta_key','closing');
        $query->set('meta_type','DATE');
        $query->set('orderby','meta_value');  
    }  
} 

Then, before display, change to the display format you wish.

// Convert from MySQL format to the desired format for display
// For example, use the date format configured in WordPress settings
$date = date( get_option('date_format'), 'the_date_value' );

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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