searching between dates
I am having some trouble with one of my advanced custom fields. I have a custom post type that I have added some ACFs to. The post item is a news item with various custom fields (one of which is called news_date) – the date is stored as yyyy-mm-dd and perhaps this is where I am going wrong. I am having trouble finding an example to work from but my code is as follows and it should theoretically pick up the 4 news items that i have in there.
$start = '2010-01-01'; $end = '2013-12-31'; $args = array( 'post_type' => 'news', 'posts_per_page' => -1, 'orderby' => 'meta_value', 'order' => 'ASC', 'meta_key' => 'news_date', 'meta_query' => array( array( 'key' => 'news_date', 'value' => array($start, $end), 'compare' => 'BETWEEN', 'type' => 'DATE' ) ) ); $news_query = new WP_query(); $news_query->query($args);
This is giving me the following SQL:
SELECT wp_posts.* FROM wp_posts INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) WHERE 1=1 AND wp_posts.post_type = ‘news’ AND (wp_posts.post_status = ‘publish’ OR wp_posts.post_status = ‘private’) AND (wp_postmeta.meta_key = ‘news_date’
AND (mt1.meta_key = ‘news_date’ AND CAST(mt1.meta_value AS DATE) BETWEEN ‘2011-11-31’ AND ‘2011-10-01’) ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC
Any advice on what I may be doing wrong would be much appreciated.
- The topic ‘searching between dates’ is closed to new replies.