I want to create a view with a single exposed date filter, and have it select only nodes where that date falls between the from and to dates on a CCK Date field.

For instance, I have a content type called Schedule, with a single date field (field_dates) that has both "From" and "To" values. Let's say I have the following two nodes:

Schedule-1: nid=1; field_dates_value=2010-10-01; field_dates_value2=2010-11-30
Schedule-2: nid=2; field_dates_value=2010-10-22; field_dates_value2=2010-10-23

I want to create a view with one exposed date filter, called "Date". If Date=2010-10-06, then the view should return Schedule-1. If Date=2010-10-22, then the view should return Schedule-1 and Schedule-2. If Date=2010-12-02, then the view should be empty.

I can set up two separate filters—one filtering on Date>=field_dates_value and one filtering on Date<=field_dates_value2—but that means I have two separate exposed filters and I only want people to enter a single date.

Comments

jstoller’s picture

My current workaround is to set a single exposed filter on the "From" date, then alter the database query using a views hook to add my "To" date filter. The code in my module is as follows:

<?php

function MY_MODULE_views_pre_execute(&$view) {
	if ($view->name == 'movie_shedule') {
		/* If this is an Movie Schedule view, alter the database query so the 
		 * exposed date filter finds schedules where the selected date is 
		 * between the from and to values. */
		$myquery = $view->build_info['query'];
		$pattern = "/<= \'([0-9]{4}-[0-9]{2}-[0-9]{2})\'\)/";
		$replacement = "<= '$1') AND (DATE_FORMAT(STR_TO_DATE(node_data_field_dates.field_dates_value2, '%Y-%m-%%dT%T'), '%Y-%m-%%d') >= '$1')";
		$myquery = preg_replace($pattern, $replacement, $myquery);
		$view->build_info['query'] = $myquery;
	}
}

?>

This appears to work, but it seems like there should be an easier way to do this from within the Views GUI.

capellic’s picture

Thanks for your solution. For those following along out there, defined your field as only being the "from" date and then set it to "is less than or equal to". The above code is looking for a pattern based on this and then adding the "to" date where it must be greater than the filter date. Of course you'll have to also update the code to match you field name.

Thanks, @jstoller!

13rac1’s picture

Component: Date CCK Field » Code
Status: Active » Closed (works as designed)

I've made this functionality into a contrib module for D7: https://drupal.org/project/views_between_dates_filter

Closing this old support request.

jstoller’s picture

Version: 6.x-2.6 » 7.x-2.x-dev
Category: support » feature
Status: Closed (works as designed) » Active

I'm re opening this as a feature request. I'm glad to know that there is a module out there now, but this really should be a standard feature and not require installation if another module, IMHO.

bdanin’s picture

Issue summary: View changes
Status: Active » Fixed

Doesn't this already exist in Views 3.x ?
I recently had a project where I needed this functionality, and was able to use date as an exposed filter with the operator "is between"
Other than some odd conflicts with date-picker (pop-up), this worked as expected. Another note, it's best to use date fields as opposed to published dates, due to date having more ability to choose granularity in your sorting.
Note: this requires using the date_views module (part of date), but it still works and is relatively easy to setup.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

casper83’s picture

I am having problem after saving the filter to raise a handler error instead of the field !>