While building a report recently, we found that the date filters were not functioning the same way. We opened up a support ticket and found that the reason is due to how the data is stored. Devs are aware of the issue, but do not currently have a plan to resolve it. See the example below.
We have a custom object with a date field called Cancellation Effective. We wanted to find all cancellations in the month of September. Initially we used this filter:
This should give us all records where the effective date is in September, however it excluded any records with a cancellation date of 9/1/21.
So we altered it to just this filter to see what would happen:
The record with a date of 9/1/21 still did not show.
So then we tried this filter:
The record with a date of 9/1/21 showed up with this filter.
It was explained that the reason is because all dates are stored with a time of midnight UTC on the backend, which is standard. However, the "is after" filter applies your timezone to the dates and the "is between" filter does not. This results in the 9/1/21 date to show up as 8/31/21 where the filter is concerned. We could create a Date Time field with the API to resolve this issue, but that shouldn't have to be done to make these filters work as designed.
This will result in completely inaccurate reporting for our users. I need to be able to trust that all filters are accurately representing the data in the same ways. If the date shows up as 9/1/21 with my time zone when viewing the record, it should show up as 9/1/21 when I'm using a filter to locate that same record. It doesn't make sense for it to be designed this way.