Filter Between two date fields

Question

Hello,

We are trying to create a selection on a layer based on a specified date or date range. For reference we have two date fields we want to compare between: Start Date and End Date. The user wants to be able to select a date (or date range) and display any records that are active between those dates.

The layer is work projects and the user wants to enter a date (or date range) to select out any projects that are active (within the start or end date). For example if there is a project with a start date of 1/1/2020 and end date of 12/23/2020 if the user enters date 3/5/2020 then that project should be returned. We have tried using the filter widget, however there are several issues with this:

1. The date picker is missing certain dates when using the between option (October missing the 31st)

2. The between functionality is not returning the expected records-- see below, it is returning records outside the selected dates

3. We do not want the user to have to enter the date range twice. Ideally they could just enter a single date (or date range) and the filter would return any "Active" projects (i.e. compare the start and end date and if the user selection is within that range then it is active).

How we are able to set it up now (not working/not user friendly)

Thank you for any help,

Hayleigh

Samd603 · Answer

Did you find a solution to this problem?

HayleighFisher · Answer

Yes we are using the time slider as well but unfortunately the customer always wants more, so they still want the filtering capability directly. We have started working on a custom solution, thank you again for your help and input!

- Hayleigh

jcarlson · Answer

Ah, that's right. It's not an easy thing to filter, but it doesn't seem like it would be too tricky to have a custom widget that applied the entered date values against two fields.

Have you looked at designating the two fields as the "start" and "end" dates in the layer's time settings and using a time slider widget?