Problem with date comparison for custom fields

Question

This question follows on from: https://wordpress.stackexchange.com/questions/11390/can-you-do-a-date-comparison-on-a-custom-field-and-query-two-or-more-custom-taxo

and a similar thing here: https://stackoverflow.com/questions/7080352/multiple-custom-fields-stored-as-meta-keys-how-to-connect-with-the-post/

I'm trying to compare a start and end date which are custom fields in a post type of exhibitions which is set up using a jquery date picker with dateFormat : 'dd-mm-yy'

$today = date('Y-m-d', strtotime('-6 hours'));      
$args = array(
    'showposts' => 1,
    'orderby' => rand,
    'post_type' => 'exhibition',
    'meta_query' => array(
        relation' => 'AND',
        array(
        'key' => 'startDate',
        'type' => 'CHAR',
        'value' => $today,
        'compare' => '>='
         ),
        array(
        'key' => 'endDate',
            'type' => 'CHAR',
            'value' => $today,
            'compare' => '<='
             ),
   )
);

What I don't understand is how this is being saved into the meta data - presumably it will be type CHAR and why my query doesn't seem to work correctly

Answer 1

This:

date( 'Y-m-d', strtotime('-6 hours') );

Is going to spit out something like 2011-11-11

If your date picker is using the format dd-mm-yy, then comparing the two is not going to work. dd-mm-yy, if it's what's in the field on post save/update, is what's going to be saved as the meta_value.

The meta value is likely getting saved as a string, which is fine -- you can still do date comparisons, but the formats have to match. There are a few options for solving this:

1. Convert your dates to unix time stamps before saving them in the database -- use strtotime -- do the reverse on the front end: date( 'F, j Y', $some_time_saved_in_db ); to show the dates to users. Comparing timestamps should be the most reliable.
2. Save your field with the ISO standard date format, YYYYMMDD, which works fairly well for comparing/sorting dates.