We have an ASP.NET application which allows users to report on their data. We have a number of reports that the user selects the start and end date from a datepicker and then we returned the filtered result.
By default the time from the datepicker is 00:00:00
In translating the query for the database, we have always queried as such:
WHERE Date >= @DateMin and Date < @DateMax
So if we the developers wanted to return a months worth of data (such as June) we would naturally choose 1st June as our start date and 1st July as the end date. Its only in watching some users work that we realised that they would be searching for a months worth of data by using 1st June and 30th June as start/end dates. Therefore losing a whole day's worth of data from their report.
I've been trying to find out if there are any best practices for handling the time when a user selects only the date. Should we be defaulting the end date time to 23:59:59? Is that what other products do?
Reporting on data and allowing users to select the date range seems such a common occurrence that I'm surprised my internet searches haven't returned anything. So how is it best to handle the time portion when a user is selecting a date range?