wp custom field date format compare

Question

How do I possible to work with this code to work with date format Y-n-j

Value

start_date = 2011-1-20
end_date   = 2011-1-30

Here are the original function came from this Query between 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('Y-n-j'); /* I change this format to date('Y-n-j')*/
    $where .= " AND startdate.meta_value <= $today
    AND enddate.meta_value >= $today";
    return $where;
    }

This function is working with UNIX TIME STAMP date('Ymd') and when I change date format it doesn't work with date('Y-n-j') credit @goldenapples

Answer 1

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.

Answer 2

Try to put values for comparison in quotes, those minuses might be getting it interpreted as mathematical expression instead of string:

$where .= " AND 'startdate.meta_value' <= '$today'
    AND 'enddate.meta_value' >= '$today'";