WPQuery Date and ACF

• djoo

• July 20, 2021 at 7:31 am

I’m creating a Query, and i need to filter by an ACF date field. In the database the value (in wp_post_meta table) is like this : MM/DD/YYYY

I have few rooms with, as a value in the field date_available_from, this :

06/01/2021
08/21/2021
08/31/2021
09/06/2021
07/30/2022
10/31/2021
11/31/2021
12/31/2021
01/31/2022
03/10/2022

And then i do my query like this :

$args[1] = array(
'posts_per_page' => -1,
'tax_query' => array(
    array(
        'taxonomy' => 'cities',
        'field'    => 'slug',
        'terms'    => $term_slug,
    ),
),
'post_type' => 'room',
'meta_query' => array(
    'relation' => 'AND',
    'date_available_clause' => array(
        'key' => 'date_available_from',
        'value' => $date_from_format_search,
        'compare' => '<=',
    ),
),
'orderby' => array(
'date_available_clause' => 'ASC',
)
);

Problem 1 : The clause doesn’t works

ex : date_available_from <= 07/19/2021 give me :

01/31/2022
03/11/2022
06/01/2021

date_available_from >= 07/19/2021 give me :

07/30/2022
08/31/2021
09/06/2021
10/01/2021
10/31/2021
11/30/2021
12/31/2021

And my sort, is always by month (not by date)

How can i edit my code than let the filter and sort alright ?
I think the problem is that ACF put the format “MM/DD/YYYY” in database. Is there a way to change it ?

Thanks

• jonnymorris

• July 20, 2021 at 4:42 pm

You might try to explode the returned date value and work with the separated values for month, day, and year.

• djoo

• July 21, 2021 at 7:15 am

I thought will be easier to change the format of the date in database in YYYYMMDD.
But i don’t know how to do this with ACF ?

• djoo

• July 22, 2021 at 12:01 pm

So, at the end of all i followed a bit of all the advises i found online.

First, I changed the value of my fields to make sure in database is like YYYYMMMDD. The problem i had was, that the field was filled up through API and ACF could not format the date at the good format in database. I have to changed the format of the date that i send through the API. Normal people using the backend to fill up the field date would never have this problem.

Second, i add a line in my code

‘date_disponible_clause’ => array(
‘key’ => ‘date_available_from’,
‘value’ => $date_from_format_search,
‘compare’ => ‘<=’,
‘type’ => ‘DATE’ // This line
)
Now it’s working very well. Thanks.