Query between two meta values?

Edited again to demonstrate adding and removing filters:

Include these general functions in your functions.php. These are the filters you will invoke for the loops where you want to limit your query by these two meta values:

function date_check_join( $join ) {
    global $wpdb;
    $join .= " JOIN ".$wpdb->postmeta." AS startdate ON 
        (".$wpdb->posts.".ID = startdate.post_id AND
        startdate.meta_key = "start_date')
        JOIN ".$wpdb->postmeta." AS enddate ON 
        (".$wpdb->posts.".ID = enddate.post_id AND
        enddate.meta_key = 'end_date')";
    return $join;
    }

function date_check_where( $where ) {
    $today = date('Ymd');
    $where .= " AND startdate.meta_value <= $today
    AND enddate.meta_value >= $today";
    return $where;
    }

Now, on the page(s) where you want to include these filters, add them before the loops you want to filter and remove them afterward. For example:

add_filter( 'posts_join', 'date_check_join' );
add_filter( 'posts_where', 'date_check_where' );

query_posts( "yourqueryhere" );
if (have_posts()) : while (have_posts()) : the_post();
// This loop will only includes where today is between start_date and end_date
endwhile; endif;

query_posts( "anotherqueryhere" );
if (have_posts()) : while (have_posts()) : the_post();
// This loop also only includes where today is between start_date and end_date
endwhile; endif;

remove_filter( 'posts_join', 'date_check_join' );
remove_filter( 'posts_where', 'date_check_where' );

query_posts( "thirdqueryhere" );
if (have_posts()) : while (have_posts()) : the_post();
// This loop is not affected by the filters, so you can query for posts 
// where start_date is in the future, or end_date in the past, etc.
endwhile; endif;

You just need to add your conditions to the ‘posts_join’ and ‘posts_where’ filters before your search, and remove them afterwards (otherwise these conditions will be applied to everything on your site, including pages, menus, etc.)

If you can simplify this by only comparing against one field, then you can use the meta_compare attribute built into the WP_Query object. You could schedule your posts for the start_date (so they don’t show ahead of schedule), and then compare the date against the end_date custom field when you query.