How to query posts by month based on date custom field?

Question

I currently created a custom field for a custom post type that stores a date in the format dd/mm/yyyy is there a way to query the posts by the month?

I "kind of" got the functionality I need using the following query:

query_posts('post_type=attraction&meta_key=attraction_date&meta_value=10&meta_compare=LIKE');

The meta_value parameter would be the month but since I'm using LIKE to compare if I have a date like 10/04/2011 it'd be returned even though it's not from october.

I know there's a RLIKE expression in MySQL that lets you use regex for the LIKE condition but I'm not sure if wordpress lets you use it in any way.

Thanks in advance!

Answer 1

It is not clear what you're using as a comparison, but WP Query supports meta query comparisons and even has a DATE type.

For example:

    $query = new WP_Query( array( 
                       'post_type' => 'attraction',
                       'meta_key' => 'attraction_date', 
                       'meta_value' => '10', 
                       'meta_compare' => 'LIKE', 
                       'type' => 'DATE' ) );

Using 10 is not going to get you very far, you should use a timestamp.

I have not tested the date parameter in WP QUERY, it should be using/converting all dates to a unix timestamp using strtotime, if not I would suggest doing so.

http://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters
http://php.net/manual/en/function.strtotime.php