Use meta query to check if a custom field is between two dates

  • Hey all,

    I have a custom field on a set of posts called ‘event_date’, and I need to only display posts where the event is in the next 4 weeks. I’m trying to use ‘meta_query’ to do this, but it’s not playing ball, so I wonder if someone can help?

    My query –

    $args = array(
	'metakey' => 'event_date',
	'orderby' => 'metavalue',
	'order' => DESC,
	'posts_per_page' => 3,
	'post_type' => POST_TYPE_NEWS,
	TAXONOMY_NEWS => 'firm-events-cat',
	'meta_query' => array(
		array(
			'key' => 'event_date',
			'value' => array(date('d/m/Y'), date('d/m/Y', strtotime('28 days'))),
			'compare' => 'BETWEEN',
			'type' => 'DATE'
		),
	)
);
$events = new WP_Query($args);

    The meta query portion of the SQL that is generated –

    AND ((
    wp_postmeta.meta_key = 'event_date'
    AND CAST(wp_postmeta.meta_value AS DATE) BETWEEN '11/05/2012' AND '08/06/2012'
))

    I suspect it is because we are effectivly checking to see if a date falls between two strings, but I don’t know how to fix that using ‘meta_query’, so any help is appriciated.

    Thanks.

Viewing 1 replies (of 1 total)

  • You cannot compare dates in dd/mm/yyyy format. For example, 11/01/1900 would compare greater than 09/30/2012.

    To get a valid comparison, dates must be in yyyy/mm/dd format.

    The CAST function will only convert a string to a date if the string is in year first format.

    The most straightforward thing to do is store the custom field in year first format. If you cannot do that, you will need to use filters to form the query on a rearranged field.

Viewing 1 replies (of 1 total)
  • The topic ‘Use meta query to check if a custom field is between two dates’ is closed to new replies.