Reply To: Query between dates using Date Picker fields

• robbiegod

• June 20, 2014 at 1:24 am

I don’t see any difference between date(‘Ymd’) and current_time(‘Ymd’); They both output todays date.

It seems to do what I wanted this to do was even more involved. The tricky part was when an event spanned multiple months. Say an Event would start in May and end in July. The event would not show up in June because it was out of the scope of the range of the event.

So, a very smart fellow by the name of “keesiemeijer” over at wordpress.org helped me get the query correct.

Here is the solution:

Add this to your functions.php file – modify as needed:

function get_meta_sql_date( $pieces, $queries ) {
    global $wpdb;

    // get start and end date from query
    foreach ( $queries as $q ) {

        if ( !isset( $q['key'] ) ) {
            return $pieces;
        }

        if ( 'event_start_date' === $q['key'] ) {
            $start_date = isset( $q['value'] ) ?  $q['value'] : '';
        }
        if ( 'event_end_date' === $q['key'] ) {
            $end_date = isset( $q['value'] ) ?  $q['value'] : '';
        }
    }

    if ( ( '' === $start_date ) || ( '' === $end_date ) ) {
        return $pieces;
    }

    $query = "";

    // after start date AND before end date
    $_query = " AND (
        ( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s) )
        AND ( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) <= %s) )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date );

    // OR before start date AND after end end date
    $_query = " OR (
        ( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
        AND ( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s) )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date );

    // OR before start date AND (before end date AND end date after start date)
    $_query = " OR (
        ( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
        AND ( mt1.meta_key = 'event_end_date'
            AND ( CAST(mt1.meta_value AS DATE) <= %s )
            AND ( CAST(mt1.meta_value AS DATE) >= %s )
        )
    )";
    $query .= $wpdb->prepare( $_query, $start_date, $end_date, $start_date );

    // OR after end date AND (after start date AND start date before end date) )
    $_query = "OR (
        ( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s ) )
        AND ( $wpdb->postmeta.meta_key = 'event_start_date'
            AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s )
            AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s )
        )
    )";
    $query .= $wpdb->prepare( $_query, $end_date, $start_date, $end_date );

    $pieces['where'] = $query;

    return $pieces;
}

Then add this to your page where you do your query.

<?php
add_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );
$all_events = array (
    'post_type' => 'events',
    'posts_per_page' => 50,

    // creates the meta sql join and where clauses
    // which will be filtered in functions.php
    // must be two meta_query arrays
    'meta_query' => array(
        'relation' => 'AND',
        array(
            'key'       => 'event_start_date',
            'compare'   => '>=',
            'value'     => '20140601',
            'type'      => 'DATE'
        ),
        array(
            'key'       => 'event_end_date',
            'compare'   => '<=',
            'value'     => '20140630',
            'type'      => 'DATE'
        )
    ),
);

$date_query = new WP_Query( $all_events );
remove_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );
?>

So, holly molly was this involved. I would recommend that they add a similar solution to the documentation on the ACF website.

This worked for me.