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?

  • Shouldn't your query be WHERE Date >= @DateMin and Date < @DateMax? – Dan Pichelman Aug 6 '15 at 14:54
  • Woops! Yes it should, have just edited that thanks @DanPichelman – Kirsten Aug 6 '15 at 15:03

Time on a date-only object is usually midnight. So if you add one to your ending day, you'll get the correct range; it will include the entire last day, up to midnight.

  • So are you saying its best to assume the user is always selecting the last day they want included in the range? – Kirsten Aug 6 '15 at 15:10
  • That would depend on what your software requirements say. But generally, yes, I think people assume that the last day is inclusive. – Robert Harvey Aug 6 '15 at 15:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.