I have 2 date fields, schedule1 and schedule2. I have created a View to filter nodes containing dates in between two values for either of these fields. If I select both date fields (whether using OR or AND) the view comes up with no results. If I only include one of the date fields, however, the nodes are returned correctly. I have tried the latest DEV version (which appears to be the same as 2.4) and have reviewed all of the relevant open issues. I have been troubleshooting this for 3 days and cannot figure out why this is happening or a workaround.

This worked in Date 2.3 but is broken after Date 2.4 -- possibly due to the Arguments/Filters fix?

Notice the incorrect reference to the field value in the latter query: node_data_field_schedule2</b>.field_schedule1</b>_value ... the query is requesting schedule1 value from schedule2 field.

Here is the SQL query that works:

SELECT node.nid AS nid,
   node.title AS node_title,
   node_revisions.body AS node_revisions_body,
   node_revisions.format AS node_revisions_format,
   node.type AS node_type,
   node.vid AS node_vid
 FROM node node 
 LEFT JOIN content_field_schedule2 node_data_field_schedule2 ON node.vid = node_data_field_schedule2.vid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE ((node.status <> 0) AND (node.type in ('event')))
    AND (DATE_FORMAT(node_data_field_schedule2.field_schedule2_value, '%Y-%m-%d') >= '2009-10-01' AND DATE_FORMAT(node_data_field_schedule2.field_schedule2_value, '%Y-%m-%d') <= '2009-11-01')
   ORDER BY node_title ASC

and here is the SQL Query that doesn't work

SELECT node.nid AS nid,
   node.title AS node_title,
   node_revisions.body AS node_revisions_body,
   node_revisions.format AS node_revisions_format,
   node.type AS node_type,
   node.vid AS node_vid
 FROM node node 
 LEFT JOIN content_field_schedule2 node_data_field_schedule2 ON node.vid = node_data_field_schedule2.vid
 LEFT JOIN content_field_schedule1 node_data_field_schedule1 ON node.vid = node_data_field_schedule1.vid
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE ((node.status <> 0) AND (node.type in ('event')))
    AND ((DATE_FORMAT(node_data_field_schedule2.field_schedule2_value, '%Y-%m-%d') >= '2009-10-01' AND DATE_FORMAT(node_data_field_schedule2.field_schedule2_value, '%Y-%m-%d') <= '2009-11-01') OR (DATE_FORMAT(node_data_field_schedule2.field_schedule1_value, '%Y-%m-%d') >= '2009-10-01' AND DATE_FORMAT(node_data_field_schedule2.field_schedule1_value, '%Y-%m-%d') <= '2009-11-01'))
   ORDER BY node_title ASC

Comments

pauldawg’s picture

(please disregard the bold tag in the post)

pauldawg’s picture

If anyone is available to lend a helping hand on this one it would be greatly appreciated! Somehow I missed this bug in testing before upgrading my Production server, and I have a need for a fix or workaround in the next week if possible.

sinmanteca’s picture

I think this might be a duplicate of this: #580546: Date 6.x-2.4 maybe broke CCK date Views2 filter

The solution in comment #4 worked for me.

pauldawg’s picture

Excellent! This seems to have worked for me as well! Thanks so much Leinado!! Shall we create a patch and submit for review? Which issue should remain open?

arlinsandbulte’s picture

Status: Active » Closed (duplicate)