Sorting posts by custom date fields (non standard date format)

Question

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'
));

Answer 1

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 );