order post my meta value m/d/y format with year as included value

2 Views

current order:

01/01/2013
12/07/2012
12/08/2012
.
.
.
.
12/31/2012
————-

Read More

01/01/2013 should appear last

My code: 

$paged = ( get_query_var('paged') ) ? get_query_var('paged') : 1;
$today = date('m/d/Y');
query_posts(array(
    'post_type' => 'post',
    'category_name' => 'flavors',
    'paged' => $paged,
    'meta_key' => 'flavor_date',
    'orderby' => 'meta_value',
    'order' => 'ASC',
         'meta_query' => array(
        array(
        'key' => 'flavor_date',
        'value' => date("m/d/Y", strtotime('-6 hours')),
        'compare' => '>=',
        'type' => 'CHAR'
        )
    )
));
if (have_posts()) :
while (have_posts()) : the_post();

Function Used: 

add_filter('posts_join', 'new_join' );
function new_join($pjoin){
    if(is_category()){
        global $wpdb;
        $pjoin .= "LEFT JOIN (
SELECT *
FROM $wpdb->postmeta
WHERE meta_key =  'flavor_date' ) AS metasort
ON $wpdb->posts.ID = metasort.post_id";
    }
    return ($pjoin);
}

 add_filter('posts_orderby', 'new_order' );
function new_order( $orderby ){
    global $wpdb;
    if(is_category()){
        $orderby = "metasort.meta_value ASC";
    }

    return $orderby;
}
Post Views: 2

Related posts

Leave a Reply

1 comment

  1. The only date format that sorts into human calendar order is “year/month/day”. The separators shouldn’t matter. If you left your dates in UNIX time format, as you get from strtotime, they would sort appropriately as well.

    I would recommend re-organizing your code to match one or the other of those formats. I would go further and recommend that you use unix time. If you save those values in UNIX time format, you can easily display them however you want. That is, just use date("m/d/Y", $unixtimeformatedstring) when you display instead of when you save.

    I know unixtime is hard to read, but it plus the PHP functions that can manipulate it, is a miracle worker when it comes to dealing with dates (as long as you don’t need dates too far in the past 🙂 )