Hi @idontknowhow:
You’ve focused on trying to solve the problem in PHP when in fact you needed to focus on the SQL. Your first problem was you reversed the “polarity” (which is a term I made up just now, but it kinda fits); you were looking for both a start date that was greater than today and an end date the was earlier than today; a logical impossibility.
Second problem ignoring the reversed polarity was more subtle; you were comparing dates with equal length strings so you could get a false positive. This SQL illustrates the problem:
SELECT * FROM wp_posts WHERE '2011-9-20' < '2011-11-20'
The WHERE criteria “looks” true and thus the query should return all records when in fact it returns none and that is because '2011-9' < '2011-1' is actually false.
So here’s a working replacement for your date_check_where() function that uses the SQL DATE() function to normalize the strings in dates into real date values so they can be compared and it of course fixes the polarity issue I mentioned at first:
add_filter( 'posts_where', 'date_check_where' );
function date_check_where( $where ) {
$today = date('Y-m-d');
$where .=<<<SQL
AND DATE(startdate.meta_value) >= DATE('{$today}')
AND DATE(enddate.meta_value) <= DATE('{$today}')
SQL;
return $where;
}
Note I used date('Y-m-d') instead of date('Y-n-j') because I find it cleaner to use, and it doesn’t need to make the meta fields because of the DATE() function.