I have a view that should select all nodes with an event date less than 'now' (I'm not using the event module). It works fine if I select any date equal to or greater than, but if I select less than in the filter the value of 'now' turns from today's date into '0001-01-01'. It was working in the prior version. I have upgraded to the Dev version from Nov 19 and cleared the cache and it still is not working. I'm sorry that I am not a programmer or I would try to help investigate it further.




Here is the export of the view and the query :

$view = new view;
$view->name = 'Past_Block';
$view->description = 'Past Events Block';
$view->tag = '';
$view->view_php = '';
$view->base_table = 'node';
$view->is_cacheable = FALSE;
$view->api_version = 2;
$view->disabled = FALSE; /* Edit this to true to make a default view disabled initially */
$handler = $view->new_display('default', 'Defaults', 'default');
$handler->override_option('fields', array(
  'name' => array(
    'label' => '',
    'link_to_taxonomy' => 1,
    'exclude' => 0,
    'id' => 'name',
    'table' => 'term_data',
    'field' => 'name',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('sorts', array(
  'field_event_date_value' => array(
    'order' => 'DESC',
    'id' => 'field_event_date_value',
    'table' => 'node_data_field_event_date',
    'field' => 'field_event_date_value',
    'relationship' => 'none',
  ),
  'field_event_date_value2' => array(
    'order' => 'DESC',
    'id' => 'field_event_date_value2',
    'table' => 'node_data_field_event_date',
    'field' => 'field_event_date_value2',
    'relationship' => 'none',
  ),
));
$handler->override_option('filters', array(
  'type' => array(
    'operator' => 'in',
    'value' => array(
      'event' => 'event',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'id' => 'type',
    'table' => 'node',
    'field' => 'type',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
  'date_filter' => array(
    'operator' => '<',
    'value' => array(
      'value' => NULL,
      'min' => NULL,
      'max' => NULL,
      'default_date' => 'now',
      'default_to_date' => '',
    ),
    'group' => '0',
    'exposed' => FALSE,
    'expose' => array(
      'operator' => FALSE,
      'label' => '',
    ),
    'date_fields' => array(
      'node_data_field_event_date.field_event_date_value' => 'node_data_field_event_date.field_event_date_value',
      'node_data_field_event_date.field_event_date_value2' => 'node_data_field_event_date.field_event_date_value2',
    ),
    'date_method' => 'AND',
    'granularity' => 'day',
    'form_type' => 'date_select',
    'default_date' => 'now',
    'default_to_date' => '',
    'id' => 'date_filter',
    'table' => 'node',
    'field' => 'date_filter',
    'override' => array(
      'button' => 'Override',
    ),
    'relationship' => 'none',
  ),
));
$handler->override_option('access', array(
  'type' => 'none',
));
$handler->override_option('title', 'Past Events');
$handler->override_option('header_format', '2');
$handler->override_option('header_empty', 0);
$handler->override_option('use_ajax', TRUE);
$handler->override_option('items_per_page', 0);
$handler->override_option('style_plugin', 'list');
$handler->override_option('style_options', array(
  'grouping' => '',
  'type' => 'ul',
));
$handler = $view->new_display('block', 'Block', 'block_1');
$handler->override_option('block_description', '');
$handler->override_option('block_caching', -1);
SELECT node.nid AS nid,
   node_data_field_event_date.field_event_date_value AS node_data_field_event_date_field_event_date_value,
   node_data_field_event_date.field_event_date_value2 AS node_data_field_event_date_field_event_date_value2,
   term_data.name AS term_data_name,
   term_data.vid AS term_data_vid,
   term_data.tid AS term_data_tid
 FROM node node 
 LEFT JOIN content_type_event node_data_field_event_date ON node.vid = node_data_field_event_date.vid
 LEFT JOIN term_node term_node ON node.vid = term_node.vid
 LEFT JOIN term_data term_data ON term_node.tid = term_data.tid
 WHERE (node.type in ('event'))
    AND ((DATE_FORMAT(STR_TO_DATE(node_data_field_event_date.field_event_date_value, '%Y-%m-%dT%T'), '%Y-%m-%d') < '0001-01-01') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_event_date.field_event_date_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') < '0001-01-01'))
   ORDER BY node_data_field_event_date_field_event_date_value DESC, node_data_field_event_date_field_event_date_value DESC

Comments

jgreen’s picture

This problem is occurring for me as well. Seems to use '0001-01-01T00:00' for the compared date rather than now for either "less than" or "less than or equal to" operators. I tried all the other operators and its seems fine, just those 2 are affected. I'm using drupal 6.6.

julianna’s picture

I'm getting the same problem with Views 6-2.1, Drupal 6.6.

The query shows

(DATE_FORMAT(STR_TO_DATE(node_data_field_event_ends.field_event_ends_value2, '%Y-%m-%dT%T'), '%Y-%m-%d') < '0001-01-01')

This happens when using 'now' as the date default, but also if an absolute date is pulled. I tried using the "between" option as a workaround, but the "to date default" doesn't accept 'now' as a value (outputs as &#039;now&#039;)

For the 'date greater than' option, the correct date is pulled.

choster’s picture

Also reported in the issues queue for Date at http://drupal.org/node/337764 .

merlinofchaos’s picture

Status: Active » Closed (won't fix)

If you only read one of these submission guidelines, read this one. (Then please read the rest) If your problem deals exclusively with fields or filters provided by another module (CCK, Image, VotingAPI are common) please post the issue under the queue for that module first; all modules are responsible for telling Views about their own fields. It's possible Views IS at fault, but the module maintainers are the best people to make that determination, and they can kick the issue into the Views queue with an explanation of what's wrong if that is the case.

The number of people who fail to read that just amazes me.

mikeque’s picture

I too am experiencing this bug with the latest installations. Greater than works just fine, but less than does not. Has anyone discovered a worthwhile workaround to this problem?

karens’s picture

This is fixed in -dev today in the Date module, and there were several issues in Date already about this. Posting a 'me too' on a duplicate issue in the wrong queue won't help anything. The fix is in today's -dev and will be in the next release.