Monthly Archives: September 2014

Sort On Custom Field Date In ‘mm/dd/yyyy’ Format

There are often posts in the WP Support Forum asking how to sort on a Custom Field which contains a date in ‘mm/dd/yyyy’ format. This is not straight forward because you cannot sort directly on a date in that format.
Consider the following dates:

02/10/2010
08/25/2014
07/01/2015

These dates are in ascending order, but if sorted in the ‘mm/dd/yyyy’ format, the order would be:

02/10/2010
07/01/2015
08/25/2014

This is obviously incorrect.  To sort in the proper order, dates must be in a format with the year first, followed by month and then day, i.e. the most significant field (the one which changes slowest) must be first.

So, the easiest way to get your posts to sort on a date in a Custom Field is to store the value in ‘yyyy/mm/dd’ format.

But, what if you already have a large number of posts with the date in ‘mm/dd/yyy’ format?  The answer then is to use filters on the query to add a sort key with the date rearranged into the proper order.  Here is one way to do that.

First, add these functions to functions.php:

// Filter functions to modify a query.
function mam_posts_fields ($fields) {
   global $mam_global_fields;
   // Make sure there is a leading comma
   if ($mam_global_fields) $fields .= (preg_match('/^(\s+)?,/',$mam_global_fields)) ? $mam_global_fields : ", $mam_global_fields";
   return $fields;
}
function mam_posts_join ($join) {
   global $mam_global_join;
   if ($mam_global_join) $join .= " $mam_global_join";
   return $join;
}
function mam_posts_where ($where) {
   global $mam_global_where;
   if ($mam_global_where) $where .= " $mam_global_where";
   return $where;
}
function mam_posts_orderby ($orderby) {
   global $mam_global_orderby;
   if ($mam_global_orderby) $orderby = $mam_global_orderby;
   return $orderby;
}
add_filter('posts_fields','mam_posts_fields');
add_filter('posts_join','mam_posts_join');
add_filter('posts_where','mam_posts_where');
add_filter('posts_orderby','mam_posts_orderby');

Then use code similar to this to do the query:

// Add a sort key field containing the Custom Field mm/dd/yyyy rearranged to yyyy/mm/dd
$mam_global_fields = ', CONCAT(SUBSTRING(pm.meta_value,7,4), "/", SUBSTRING(pm.meta_value,1,5)) as sort_key';
// Join the postmeta table and set the sort order.
// Be sure to use your own meta_key name in the join!
$mam_global_join = "JOIN $wpdb->postmeta pm ON (pm.post_id = $wpdb->posts.ID AND pm.meta_key = 'eventdate')";
$mam_global_orderby = 'sort_key DESC';

// Query arguments other than 'meta_key', 'orderby', and 'order
// because these will be handled by the filters.
$args = array(
   'posts_per_page' => -1,
   'ignore_sticky_posts' => 1,
   'order' => 'DESC',
);
query_posts($args);

$mam_global_fields = $mam_global_join = $mam_global_orderby = ''; // Clear the filters

if ( have_posts() ) {
   while ( have_posts() ) {
      the_post();
      // Use your own code for the display
      the_title();
      $eventdate = get_post_meta($post->ID, 'eventdate', true);
      echo " Event Date: $eventdate SORT KEY:$post->sort_key<br />";
   }
}