0

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' });
});
  • I believe this sort of query has to use date in YYYYMMDD format to work properly... Try converting it and using that way – Bysander Feb 4 '15 at 13:23
  • This page will probably help you – Bysander Feb 4 '15 at 13:25
  • @Bysander - The correct format is YYYY-MM-DD, but that only applies if you are using the BETWEEN comparrison. – David Gard Feb 5 '15 at 10:51
  • 1
    Hi @DavidGard - Thanks - I now know this - I have been struggling with something similar this week, and in the end opted to use the between arguments as a lot of the date arguments seem a bit counter intuitive to me 'inclusive' => true to name but one.... I had it working with YYYYMMDD from reading the StackOverflow link posted above - claimed it was posted from the codex. Since having to use more complex date queries I have changed to the correct YYYY-MM-DD format. Apologies @mistertaylor for the confusion – Bysander Feb 5 '15 at 14:25
  • Please post your solution as an answer for future reference please :-) – Pieter Goosen Feb 5 '15 at 17:22
0

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.

0

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' });
});

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.