Query multiple fields with dates

Question

So I'm trying to build a query based on events and their start and end dates. Once a user selects a year, I want to display all events that took place in that year, but also display events that might have spanned across multiple years and include that selected year. For example, if a user selects 2012. I should display all events starting in 2012, but also events that might have lasted from say 2010-2013.

What I'd like to do is query all event entries and return only events whose startDate is less than or equal to the select year AND whose end date is greater than or equal to the selected year:

{% set startDateParam = '<=' ~ yearParam ~ '-01-01' %}
{% set endDateParam =  '>=' ~ yearParam ~ '-01-01' %}

{% set pastEntries = craft.entries({ 
   startDate: startDateParam,
   endDate: endDateParam
}).section('events').order('endDate desc') %}

However, this doesn't seem to work. What is the correct way to query for entries that satisfy both the startDate AND endDate?

Any and all help is greatly appreciated. Thanks.

Answer 1

craft.entries isn't going to like a variable in the start or end dates.

i.e. your startDateParam should just be {% set startDateParam = yearParam ~ '-01-01' %}

And then your craft.entries is going to grab everything between the two dates.

If you're trying to grab everything that overlaps the year ends... I suspect you're going to have to do a little bit of funny business...

e.g. start date = a month before the year end, and end date is a month after the end date.

then when you do the loop, have it check both.

If start date is before the end of the year OR the end date is after the beginning of the year.

Then show all the items that meet that criteria. So if there's an event dec 12-13, 2015, the loop picks it up, but your if-check hides it. If it runs dec 30, 2015 to jan 2, 2016, the if-check shows it.

Maybe something like

{% set startDataParam = date_modify('November 1, ' ~ year-1 ~ ' 00:00:00') %}
{% set endDateParam = date_modify('February 28, ' ~ year+1 ~ ' 23:59:59') %}

{% set pastEntries = craft.entries({ 
 startDate: startDateParam,
 endDate: endDateParam
}).section('events').order('endDate desc') %}

{% for pastEntry in pastEntries %}
{% if pastEntry.startDate => date_modify('January 1,' ~ yearParam ~ ' 00:00:00') or pastEntry.endDate <= date_modify('December 31,' ~ yearParam ~ ' 00:00:00')}
  {{ all your event details here }}
{% endfor %}

That make sense?