Comparing dates in custom field

Question

I have a function which should return the future event date:

function get_upcoming_exercises( $limit = -1 ) {    
    $args = array(
        'post_type' => 'px_event',
        'posts_per_page' => $limit,
        'post_status' => 'publish',
        'meta_query' => array(
            array(
                'key' => '_px_event_date',
                'value' => date('m-d-y'),
                'compare' => '>=',
            ),
        ),
        'orderby' => 'key',
        'order' => 'DESC',
    );

    $query = new WP_Query( $args );       
    return $query;
}

the dates are stored like strings(m-d-y):

10-25-2012

01-03-2013

The function doesn't work fine, because, if I test, for example, with dates greater than 11-22-2012 it doesn't show 01-03-2013.

If I execute the comparison in mysql it fails too:

select * from wp_postmeta
where meta_key = '_px_event_date'
and meta_value >= '11-22-2012'

What am I doing wrong?

Answer 1

Your date format is in the wrong order. Think of it from a strictly numerical perspective- 11222012 > 1032013

Dates should follow the MySQL date format in descending units- yyyy-mm-dd

Answer 2

This is old, but simply switching

date('m-d-y') to date('Y-m-d')

Should work. Notice CAP Y.

Answer 3

I've finally modified the function to use a sql query:

function get_upcoming_exercises( $limit = -1 ) {
    global $wpdb;

    $exercises_query = "select m.meta_value, p.*  
                        from $wpdb->postmeta m, $wpdb->posts p
                        where m.meta_key = '_px_exercise_date'
                        and p.ID = m.post_id
                        and p.post_status = 'publish'
                        and STR_TO_DATE(meta_value, '%m-%d-%Y') >= STR_TO_DATE('" . date('m-d-y') . "', '%m-%d-%Y')
                        order by STR_TO_DATE(meta_value, '%m-%d-%Y') desc " ;
    if ($limit != -1)
        $exercises_query .= " limit ".$limit;

    $exercises = $wpdb->get_results( $exercises_query );

    return $exercises;
}