Filter query based on date in custom field

Question

I have a custom post type jobs and custom field with key expiry-date which the user enters the date after which the post will no longer be displayed.

Here's my code so far...

$loop = new WP_Query( array(
            'post_type' => 'jobs',
            'posts_per_page' => -1,
            'order' => 'ASC',
            'orderby' => 'date',
            'meta_query' => array (
                'key' => 'expiry-date',
                'value' => date('d/m/Y',strtotime("today")),
                'type' => 'DATE',
                'compare' => '>='
            )
        ));

if (have_posts()) : while ($loop->have_posts()) : $loop->the_post();
    [display content]
endwhile; else:
    [display "no vacancies" message]
endif;

The date in expiry-date is stored in the format dd/mm/yyyy (although some posts may have this field empty). I'd like to only return posts in the query where the expiry date is greater than today's date (or doesn't have a date set) - I've tried various values and compare types what am I missing?

---

EDITED TO ADD SOLUTION:

In the function in functions.php which saves the meta data, I created an additional custom field expiry-date-formatted which stores the date in the correct format, or sets the value to "true" if no date set:

update_post_meta($post_id, 'expiry-date', $date);
if (!empty($date)) {
    update_post_meta($post_id, 'expiry-date-formatted', date('Y-m-d', strtotime($date)) );
} else {
    update_post_meta($post_id, 'expiry-date-formatted', 'true' );
}

and then updated meta_query:

'meta_query' => array(
                'relation' => 'OR',
                array(
                    'key'       => 'expiry-date-formatted',
                    'value'     => date('Y-m-d',strtotime("today")),
                    'compare'   => '>=',
                    'type'      => 'DATE'
                ),
                array(
                    'key'       => 'expiry-date-formatted',
                    'value'     => 'true',
                    'compare'   => '='
                )
            )

in addition, I formatted the input from jQuery-UI datepicker in the format '5 Feb 2015' to avoid confusion.

jQuery(function($) {
    $( "#ce-job-expiry-date" ).datepicker({ dateFormat: 'dd M yy' });
});

Answer 1

You can use this format of meta_query, although you'd have to change the format of all of your existing expiry-date entries.

'meta_query' => array(
    'key' => 'expiry-date',
    'value' => array($today, $UserInputExpiry),
    'compare' => 'BETWEEN',
    'type' => 'DATE'
)

The second date in the array is the later date and both $today and $UserInputExpiry should be in the format YYYY-MM-DD.

Answer 2

In the function in functions.php which saves the meta data, I created an additional custom field expiry-date-formatted which stores the date in the correct format, or sets the value to "true" if no date set:

update_post_meta($post_id, 'expiry-date', $date);
if (!empty($date)) {
    update_post_meta($post_id, 'expiry-date-formatted', date('Y-m-d', strtotime($date)) );
} else {
    update_post_meta($post_id, 'expiry-date-formatted', 'true' );
}

and then updated meta_query:

'meta_query' => array(
                'relation' => 'OR',
                array(
                    'key'       => 'expiry-date-formatted',
                    'value'     => date('Y-m-d',strtotime("today")),
                    'compare'   => '>=',
                    'type'      => 'DATE'
                ),
                array(
                    'key'       => 'expiry-date-formatted',
                    'value'     => 'true',
                    'compare'   => '='
                )
            )

in addition, I formatted the input from jQuery-UI datepicker in the format '5 Feb 2015' to avoid confusion.

jQuery(function($) {
    $( "#ce-job-expiry-date" ).datepicker({ dateFormat: 'dd M yy' });
});