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:


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


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;

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',

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

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


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

  • Erii says:

    Hi there, i saw your tips, very useful. I have a question: how can i display a post twice (i mean the same post) and than order the posts by different Custom Field Date.

    Technically a post is an event. And an event is going to be on air twice a day. So that means same post will be displayed twice (duplicate post) but every event will be ordered by different meta value (date and time):
    Lets say i have 3 posts and each of them has at least 1 meta key, and one of them has 2 meta keys.

    meta key 1 = (name) start_1, (value date and time) Y-m-d H:i
    meta key 2 = (name) start_2, (value date and time) Y-m-d H:i

    And lets say this is the output:
    = (meta start_1) 2015-07-10 12:00 – (title) Movie: The Shawshank Redemption
    = (meta start_1) 2015-07-10 15:00 – (title) Movie: Pulp Fiction
    = (meta start_1) 2015-07-10 18:00 – (title) Movie: Forrest Gump
    = (meta start_2) 2015-07-10 21:00 – (title) Movie: The Shawshank Redemption

    Now the movie “The Shawshank Redemption” is a unique post, but is displayed twice in the loop as an event. The problem is that i cant order them all by meta value (date and time).

    Thanks in advance

Leave a Reply

Your email address will not be published. Required fields are marked *