Filter between first and last day of the current month in any year

Question

I have a date field in my view and when I try to filter between the first and the last day of the month there is no result in my view preview.

Here is my settings. This filter is because I need to compare the born date from all the registered users and print only those who have the value between first and last day of the month.

Here are the screen shoots of my views settings:

First here is my date field settings:

Then the filter settings:

And finally set the filter option to get the result between first and last day of the month:

This is what I've tried till now without success. Can someone help me in this? I don't want to use a module for this. I only need this filter works.

Answer 1

I had a similar requirement a while ago. What I did was to create a small module with a hook_views_query_alter(). You need to write a bit of code, but I think is more elegant and easier to maintain than adding PHP code inside contextual filters in the views UI.

Try the following:

In your .module file

function MODULE_views_api() {
  return array(
    'api' => 3,
  );
}

In module.views.inc

//make sure you use your date field table and field name bellow
function MODULE_views_query_alter(&$view, &$query) {
    if ($view->name == 'date_test') { // replace this with your view name

        //this line removes the year from your date field
        $formula = "DATE_FORMAT(field_data_field_date.field_date_value, '%m-%d')";

        //join the field table
        $join = new views_join();
        $join->table = 'field_data_field_date';
        $join->field = 'entity_id';
        $join->left_table = 'node';
        $join->left_field = 'nid';
        $join->type = 'left';

        //add the join the the view query
        $query->add_relationship('field_data_field_date', $join, 'node');

        $first = date('m-d', strtotime('first day of this month'));
        $last = date('m-d', strtotime('last day of this month'));

        //add the field without the year
        $query->add_field(NULL, $formula, 'date_no_year');

        //add the where clauses
        $query->add_where(0, "$formula >= '$first'", array(), 'formula');
        $query->add_where(0, "$formula <= '$last'", array(), 'formula');
    }
}

Answer 2

Long story short: Use Views Partial Date. The image below demos some of its features (click the image if you don't see the animation).

Why it can't be done with Views

Views by itself can only handle Unix timestamps. Even when a relative date is used, Views' date filter uses strotime() internally to convert the relative date to a timestamp before adding the filter condition to the query.

Why it can't be done with Date Views

Date Views is a complex beast that provides a multitude of features surrounding date calculations, including support for multiple DB backends.
Theoretically Date Views could implement incomplete dates, but since it also supports relative dates and various input widgets (like Date Popup), the additional validation and logic would be extremely hard to maintain.

What we can do about it

At this point we've established that either an additional module or custom code of some form is required.
With that in mind I created Views Partial Date and will be expanding this answer in the following days to describe the approach I took.

Answer 3

Ok try this.

1. Add contextual filter "node id"
2. allow multiple values
3. add this php code as default value, when no filter value is in url:

---

$results = db_query("SELECT entity_id FROM field_data_field_FIELDNAME 
WHERE DATE_FORMAT(field_FIELDNAME_value,'%m') = DATE_FORMAT(NOW(),'%m') 
AND entity_type = 'node' AND bundle = 'NODE_TYPE'");
$ids = array();
foreach ($results as $result) {
  $ids[] = $result->entity_id;
}
return implode(',',$ids);

Related to this.

Answer 4

This article will help you (which is on drupal.org as well). Once you are configured with this, you will be able to filter the content between exactly two dates.

So you need to add a default start date as 1st of particular of month and end date as nextmonth - 1.

This needs to be done through default views settings or one can add small javascript which added default value to that particular fields.

Answer 5

Try these two options:

first day of this month

last day of this month

Putting those values into this strtotime() tester provides the appropriate dates.

Answer 6

The SQL syntax that you want to achieve is relatively straightforward using the SQL MONTH() function:

SELECT * 
FROM field_data_field_fecha_nacimiento 
WHERE MONTH(field_fecha_nacimiento_value) = 8; // August

However, there is no way for Views to create that kind of SQL expression without some kind of additional module.

Answer 7

I haven't tested this, but wouldn't it work if you choose "month" as granularity and then use a single date (the relative "now", or a specific date) as filter value?

Answer 8

why are you making them to write custom modules for this ?

This is pretty simple for: First day of current month you need to add "first day of" & for Last day of current month same way "last day of"

Read the documentation from PHP.NET carefully.

Here I am attaching screenshot of my view's date filter for relative dates.