Custom Query based on custom field of a single post

Question

I have posts which have a custom field date in the format yyyy-mm-dd. What Im trying to do is list all the posts for a specific year on the side of a single page based on the year in the custom field of this post and separated into months.

So for example if the post has a custom field date of 1990 then I want to list all posts from 1990 on the side of the page and sorted into months.

I have to use this custom field date and cannot use the Wordpress internal date function.

Below I extracted just the year from the custom field date for the query and it returns all the links to the posts from the year the single post custom field is dated. This query appears on the side of the single post page template outside of the single post loop.

Im trying to figure out how I can now sort these posts into months? So January followed by all the post links for this month, February and so on...

<?php
 $histyear = substr (get_post_meta($post->ID, "historic-date", true),0,4);
 $querystr = "
SELECT $wpdb->posts.* 
FROM $wpdb->posts, $wpdb->postmeta
WHERE $wpdb->posts.ID = $wpdb->postmeta.post_id 
AND $wpdb->postmeta.meta_key = 'historic-date' 
AND $wpdb->postmeta.meta_value = $histyear 
AND $wpdb->posts.post_status = 'publish' 
AND $wpdb->posts.post_type = 'post'
ORDER BY $wpdb->postmeta.meta_key = 'historic-date' DESC
 ";

 $pageposts = $wpdb->get_results($querystr, OBJECT);

 ?>

 <?php if ($pageposts): ?>
 <?php global $post; ?>
 <?php foreach ($pageposts as $post): ?>
 <?php setup_postdata($post); ?>

 <div class="post" id="post-<?php the_ID(); ?>">
 <h3><a href="<?php the_permalink() ?>" rel="bookmark" title="Permanent Link to <?php the_title(); ?>">
 <?php the_title(); ?></a></h3>
 </div>
 <?php endforeach; ?>
 <?php else : ?>

Answer 1

Mysql's Month function can help you on this. Assuming that your meta value is in SQL date format.

ORDER BY MONTH($wpdb->postmeta.meta_value) ASC

Answer 2

Might this help:

$args = Array(
    meta_query => Array(
      Array(
      'key' => 'historic-date',
       'value'=>Array('19900101','19901231'),
       'compare'=>'BETWEEN',
       'type'=>'DATE'
      )
        ),
'posts_per_page'=>5,
//other arg goes here
);
$query = new WP_Query($arg);

//loop query here.

The 'type' DATE works with the 'compare' value BETWEEN only if the date is stored at the format YYYYMMDD and tested with this format.

reference: http://codex.wordpress.org/Function_Reference/WP_Query#Custom_Field_Parameters