Date query based on meta_key

  • hiddenvorlon

    (@hiddenvorlon)


    14 years, 6 months ago

    I have a custom data field for my posts that is a date field. All data is formatted in mm/dd/yy or mm/dd/yyyy format. I’m trying to do a query based on the custom date field.

    $mystartdate = “06/01/2009”;
    $my_querystring = sprintf(“meta_key=mycustomdate&&meta_value>=%s”, $mystartdate );
    $my_query->query($my_querystring);

    I’m getting date values from 06/12/2008 being returned…

    Any ideas? Thank you in advance.

Viewing 4 replies - 1 through 4 (of 4 total)
  • esmi

    (@esmi)

    14 years, 6 months ago

    You’re falling into the trap of viewing the meta value as a real date. From WP’s perspective, it’s not a date. It’s a string and as such, will be sorted numerically. So ’06/12′ is placed after ’06/01′.

    If you reverse the manner in which you store these strings – from mm/dd/yy to yy/mm/dd – you should find that the sort order then falls in line with the natural date order.

    Thread Starter hiddenvorlon

    (@hiddenvorlon)

    14 years, 6 months ago

    Well, I thought that might be the case, so I tried a query date of “07/30/2009” but it pulled a date of “06/22/2008” to test the theory. “07” should be > then “06” regardless of month or year, but it still pulled the older date.

    Is there any way to convert the meta_value to a date on the fly?

    Thread Starter hiddenvorlon

    (@hiddenvorlon)

    14 years, 6 months ago

    I could get by with just pulling items based on the year… Can you do a LIKE query with the meta_value? Something like

    meta_key=mycustomdate&&meta_value LIKE %09

    This would pull all the strings that have XX/XX/2009 or XX/XX/09.

    MichaelH

    (@michaelh)

    14 years, 6 months ago

    Not much of a consolation, but that’s a request in trac: http://core.trac.wordpress.org/ticket/10800

    You would need to do a JOIN using wpdb to make it work, or figure out a filter for posts_where and posts_join.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Date query based on meta_key’ is closed to new replies.