EntityFieldQuery BETWEEN condition is not working $query-&gt;join('field_data_field_amountpaid', 'amt', 'n.nid = amt.entity_id and amt.field_amountpaid_value &gt;=500 and amt.field_amountpaid_value &lt;=5000 '); //JOIN node with Body


This is my normal query how can I call this in entity query?

I tried this way;

$query-&gt;fieldCondition('field_amountpaid', 'value', array('501','5000'),'BETWEEN');


But it's not working. Any idea?
I had the same problem than you, and was able to fix it. The real problem here is that the documentation does not provide any example.

As it was already mentioned, the BETWEEN will only work if you are using the same data types that are being stored.

In my case, the field i was searching was a date field that has a specific format. So instead of passing a timestamp i had to format that date and then pass it.

//something like
start_date = (isset($_GET['startDate'])) ?  date('Y-m-d/T00:00:00',strtotime($_GET['startDate'])) : null;
        $end_date = (isset($_GET['endDate'])) ? date('Y-m-d/T00:00:00',strtotime($_GET['endDate'])) : null;

        if (isset($start_date) &amp;&amp; isset($end_date)) {
           $query-&gt;fieldCondition('field_publication_date', 'value', array($start_date,$end_date), 'BETWEEN');
        }


Hope this helps you!