I've inherited a website with a post type courses that has a custom field 'date-start', a Date Picker.

Unfortunately the date start is stored as 'ddmmyy' and the ACF documentation implies that this code works with 'yymmdd'

Now I can't change all dates to this format because there's lots of dependent code.

So how do I get this query to work - to order the posts in order based on the 'date-start' custom field? It doesn't work with order as DESC or order as ASC. The output appears to be random order.

$posts = get_posts(array(
    'post_type'     => 'course',
    'posts_per_page'    => -1,
    'meta_key'      => 'date-start',
    'orderby'       => 'meta_value_num',
    'order'         => 'DESC'
  • 2
    it's not random, it's numerical. that's how date sorting works in MySQL and why date formats need to be yyyymmdd, so they order naturally. you can't sort on your current date format without writing your own raw SQL to handle your non-standard format. – Milo Jan 6 '15 at 17:03

You can add a posts_orderby filter to flip the meta_value:

function wpse174075_posts_orderby( $orderby, $query ) {
    if ( $query->get( 'orderby' ) != 'ddmmyy_date_format' ) return $orderby;
    if ( ! ( $order = $query->get( 'order' ) ) ) $order = 'DESC';
    global $wpdb;
    $mv = $wpdb->postmeta . '.meta_value';
    // Note SUBSTR() position (2nd) arg is 1-indexed.
    return 'CONCAT(SUBSTR(' . $mv . ', 5, 2), SUBSTR(' . $mv . ', 3, 2), SUBSTR(' . $mv . ', 1, 2)) ' . $order;

add_filter( 'posts_orderby', 'wpse174075_posts_orderby', 10, 2 );
$posts = get_posts(array(
    'post_type'     => 'course',
    'posts_per_page'    => -1,
    'meta_key'      => 'date-start',
    'orderby'       => 'ddmmyy_date_format',
    'order'         => 'DESC',
    'suppress_filters' => false,
remove_filter( 'posts_orderby', 'wpse174075_posts_orderby', 10 );
| improve this answer | |
  • Thanks for this. unfortunately it didn't work but my coding ability is not up to working out why. The posts still return in the order as per Milo's comment above. I think the best solution in the long run will be to do the spadework, ie go to a standard date format (even though it means entering all the dates again) rather than try and bend it. My understanding increases (by a small increment). Thank you bonger. – Chris Pink Jan 7 '15 at 10:33
  • Perhaps the format isn't exactly "ddmmyy" but something like "dd-mm-yy" or "dd-mm-yyyy"? – bonger Jan 7 '15 at 10:41

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.