How to use between date query?

Question

I have 2 fields in a node and I'm trying to get the expired nodes set in "Start Date" and "End Date" field.

I'm using db_select to get the start and end date values.

How can I add a condition to check if the dates are in range?

Here's my current code:-

$query = db_select('node__field_end_date', 'ed');

$query->leftJoin('node__field_start_date', 'sd', 'sd.entity_id = ed.entity_id');

// Date values showing as a formatted date
$query->addField('ed', 'node__field_end_date', 'date_end');
$query->addField('sd', 'field_start_date_value', 'date_start');

Answer 1

Something like this should do it:

db_select
  ...
  ->condition('date_field_1', $min_date, '>');
  ->condition('date_field_2', $max_date, '<=')

Answer 2

The raw SQL would be something like this,

SELECT startDate, endDate
FROM YourTable
WHERE GETDATE() between startDate and endDate;

You might could do like this with db_select

$query = db_select('node', 'n');
$query->condition(now(), array(node__field_start_date, node__field_end_date), 'BETWEEN');

I hope it helps