Meta_query and doing a SUBSTR compare on a custom date field

It is not clear if you want all three tests, or if there is a condition to select one of the three. Regardless, you can do what you want with a filter on posts_where.

Here is the filter function that would go in functions.php:

add_filter('posts_where','mam_posts_where');
function mam_posts_where ($where) {
   global $mam_global_where;
   if ($mam_global_where) $where .= " $mam_global_where";
   return $where;
}

Then, just ahead of the query_posts(), you would set the global to the desired ‘where’ condition or conditions:

$mam_global_where = " AND SUBSTR($wpdb->postmeta.meta_value,1,4) = '2012')";

Include ‘meta_key’ => ‘event-date’ in your query arguments.

Clear the filter after the query by setting the global to ”.

There is a condition to select one of the three. Thanks for the input.

I have a page called whats-on and it has optional additional paratemers like this:
(A custom post type called events has been created)

1. /whats-on/2012/02/03/event-name This scenriao I will grab a custom posty type item data based on the event name slug
2. /whats-on/2012/02/03 All custom post type events are selected that match that day YYYY-mm-dd
3. /whats-on/2012/02 All custom post type events are selected that match that month YYYY-mm
4. /whats-on/2012 All custom post type events are selected that match that year YYYY

This pretty much exactly waht I am in the procss of doing, I know the SQL to create but ideally I’d prefer to use get_posts or WP_Query

I’m basically trying to figure out if its possible to do it like this:

$args =  array(
	'meta_query' => array(
			array(
				'key' => 'event-date',
				'value' => '2012-02',
				'compare' => '>=',
				'type' => 'DATETIME'
			)
	),
        'meta_key' => 'event-date',
	'orderby' => 'meta_value',
	'post_type' => 'events',
	'posts_per_page' => 20,
	'order' => 'ASC'
);

Of course this bit wont work as effectively I’m comparing a string not a date:

array(
   'key' => 'event-date',
   'value' => '2012-02',
   'compare' => '>=',
   'type' => 'DATETIME'
)

I was wondering if the options for compare include something which will allow me to perform an action that works the same way as the SUBSTR method. I looked at the docs and as far as I can tell its doesnt support CONTAINS as that would work in the same way.

I got it thanks, it just didn’t register in my brain first time around. vtxyzzy, very much appreciated!!