API4: When searching by date range, end of range is not included
When you use SearchKit to search by date range on a datetime field (i.e. between two dates), the end date is not included in the results because it interprets 12/31/2022 as 20221231000000 instead of the expected 20221231235959. The same happens in FormBuilder with a date filter and Choose date range. This comes from API4, where ->addWhere('receive_date', 'BETWEEN', ['2022-01-01', '2022-12-31'])
is interpreted as to 20221231000000 rather than to 20221231235959.
This is quite confusing for users, e.g. when last quarter doesn't give the same results as 10/01/2022 - 12/31/2022.
I think the fix is simply to use a default time of 23:59:59 if none is specified for an end date, but I'm not sure if that might cause unexpected behaviour elsewhere.