wp custom field date format compare

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.

Related Posts: