7

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:

enter image description here

Then the filter settings:

enter image description here

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

enter image description here

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.

  • 1
    Try "first day of this month" and "last day of this month". – Huelfe Sep 22 '15 at 15:06
  • I'm sorry i've tried too and not success :( – martinezjc Sep 22 '15 at 15:25
  • try "first day of last month" and "last day of last month" – Prerit Mohan Sep 22 '15 at 15:57
  • why last month? – martinezjc Sep 22 '15 at 16:04
  • 1
    And therein lies the problem. I would like to see a good answer to this question and I will offer up a bounty if the time comes. – J. Reynolds Sep 22 '15 at 19:46
3
+250

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');
    }
}
  • Although the solution I suggest here will give you the desired behaviour, if you don't need other logic beside displaying all birthdays in the current month, I think that what @marcvangend suggested is better for your case (if it works... I haven't tested). My solution is part of something a bit more complex where I had some special cases to deal with. So, just bare in mind that although it's a bit more flexible, it may also be a bit "overkill" for what you need. – Stefan Oct 10 '15 at 17:13
  • And because I managed to find the answer that initially helped me when I had this problem, I think it would be fair for me to give credit to @Peter Lozovitskiy for drupal.stackexchange.com/a/107396/1422 – Stefan Oct 10 '15 at 18:58
  • Works perfectly and is very elegant. – J. Reynolds Oct 11 '15 at 12:23
  • @Stefan In my case do i need to add a relationship with node table? i mean, since i want to list user data only? – martinezjc Nov 30 '15 at 16:50
  • Sweet i realize i had to make the join with the users table. I will edit the answer in order to this helps someone else – martinezjc Nov 30 '15 at 16:57
4

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).

enter image description here

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.

  • @martinezjc Definitely to set limit dates of range, you should someway access the Birthday date to get to know the year, without a module or code changes it is not possible. – maciek Oct 9 '15 at 9:57
  • where can i download this sandbox project? – martinezjc Nov 30 '15 at 16:03
  • @martinezjc: It's been linked two times in the above text. Just click the link "Views Partial Date". – Fabian Iwand Dec 2 '15 at 9:23
  • @ciss Just had the opportunity to use it now and love it! – J. Reynolds Apr 17 '16 at 11:28
  • @J.Reynolds Thanks! There's also a patch for relative partial dates in the pipeline that could use some reviews: drupal.org/node/2596751 – Fabian Iwand Apr 17 '16 at 21:20
1

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.

  • 1
    I can confirm that this works. One has to use Views PHP module and set Allow multiple values to true for the nid. – J. Reynolds Oct 11 '15 at 6:45
1

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.

  • If you read OP's question and comments carefully you will see that the requirement is to filter in all dates that fall within a specific month, no matter the year of the date. So views should look at month of now = October, and return all dates that have October in them, no matter the year. – J. Reynolds Oct 11 '15 at 5:45
  • this is a broken link :( – martinezjc Nov 30 '15 at 15:50
0

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.

  • 2
    In any year. The current year would be easy. – Fabian Iwand Oct 7 '15 at 21:15
0

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.

  • 1
    Perhaps a custom Views Filter Handler? – J. Reynolds Oct 8 '15 at 4:53
  • @J.Reynolds Certainly, but that would still require a custom module. ;) – Fabian Iwand Oct 8 '15 at 8:05
  • 1
    There is 250 reputation points on offer. If you post a Views Filter Handler the points will definitely be yours. – J. Reynolds Oct 8 '15 at 9:41
  • @J.Reynolds bro no custom module for this. Check solution below – Himanshu Pathak Oct 11 '15 at 13:12
0

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?

  • 1
    I must agree here. "between first and last day of the month" is equivalent to "in the month". No need for days... – anou Oct 10 '15 at 12:09
0

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. enter image description here

  • This answer filters for dates in current month for current year only, not for current month in any year. As @ciss pointed out, there is no way to do this without some custom code. – J. Reynolds Oct 11 '15 at 13:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.