5

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'
));
3
  • 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, 2015 at 17:03
  • 1
    for those reading this years later, you can process your meta to change the date format to something that is sortable, you're not stuck if you used an incompatible date format. Also don't forget that posts already have a publish date field that can be used for this that's faster than the meta key approach, and you can use pre_get_posts to display posts that haven't reached their publish date yet if you want to go down that road. Forcing WP to try and fit a non-standard format is unnecessary
    – Tom J Nowell
    Aug 30, 2023 at 10:24
  • Worth noting that course start date and post published date are almost certainly not the same. And do you know, I cannot remember which website this applied to (or why a mass change to meta was not an option, maybe it was).
    – Chris Pink
    Aug 31, 2023 at 11:06

1 Answer 1

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 );
2
  • 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, 2015 at 10:33
  • Perhaps the format isn't exactly "ddmmyy" but something like "dd-mm-yy" or "dd-mm-yyyy"?
    – bonger
    Jan 7, 2015 at 10:41

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.