Attention: We are retiring the ASP.NET Community Blogs. Learn more >

Searching on dates

Today I had a request to implement date-range searching functionality on an existing app that I wrote.

After looking around the web for ideas on how other do this I decided to go down the path of implementing a more "heuristic" style interface that the standard dropdown boxes or popup calendars.  Below is how I planned to implement the data entry/UI side of things.  I'd be interested to hear of any other interesting alternatives for taking user input for date range searching.

Client Request

Add a the ability for users to filter records by date

UI implementation

Textbox (initially blank) that allows a user to enter a date and allows users to filter records by date range.

A dropdown list that allows a user to select a date searching option, valid options are:
     - Exact date
     - Before date
     - After date
     - Surrounding week
     - Surrounding month
     - Surrounding 6 months
     - Surrounding 12 months
 
Validation requirements

Client side validation that the date is valid
Dropdown list "disabled" if textbox is blank

Server-side implementation

- If no date is entered return resultset for all dates
Else
- If 'Exact date' is selected @startDate =  [chosen date] 00:00:01 @endDate = [chosen date] 12:59:59
- If 'Before date' is selected @startDate =  1900-01-01 00:00:01 @endDate = [chosen date] 12:59:59
- If 'After date' is selected @startDate =  [chosen date] 00:00:01 @endDate = 2099-12-01 12:59:59
- If 'Surrounding week' is selected @startDate =  ([chosen date] - 7Days) 00:00:01 @endDate = ([chosen date] + 7Days) 12:59:59
- If 'Surrounding month' is selected @startDate =  ([chosen date] - 1Month) 00:00:01 @endDate = ([chosen date] + 1Months) 12:59:59
- If 'Surrounding 6 months' is selected @startDate =  ([chosen date] - 6Months) 00:00:01 @endDate = ([chosen date] + 6Months) 12:59:59
- If 'Surrounding 12 months' is selected @startDate =  ([chosen date] - 12Months) 00:00:01 @endDate = ([chosen date] + 12Months) 12:59:59

4 Comments

  • Hi,


    Great idea and solves most of the problems for ranges that users encounter, also it is very intuitive (I'm gonna use this the next time I have to implement a date search).





    However, many users (especcially business users) would appriciate the addition (maybe in advanced mode) of actually inputing in dates and even hours for a more complete solution (that can support more advanced date search).





    One question though, why the hardcoded limit on the beforedate and afterdate? do you plan on not using the system after 2099 does Y2K ring a bell???





    All the best,


    Moshe


  • Why not actually build part of the query and stuff it into the <OPTION>s?





    <SELECT Name="dateSearch">


    <OPTION Value="theDate=$"> Exact date


    <OPTION Value="theDate<$"> Before date


    <OPTION Value="theDate>$"> After date


    <OPTION Value="DateDiff(d,$,#)>=-7 AND DateDiff(d,$,#)<=7"> Within a week


    ...


    </SELECT>





    And then, in the server code, after validating the users textual date, you just REPLACE the $ with a properly formatted version of his date for the DB you are using. Likewise, you replace any # with a proper form of today's date.





    And now you don't have the enddate problem the other noticed.





    Of course, you could use the same kind of logic on the server to avoid putting in an enddaate when it isn't needed (or begindate when it isn't needed) but why bother with logic when it isn't needed?


  • Thanks Bill... that's great!

  • One more minor "trick": Most DBs treat a date-alone as 0:00:00 on that date. So instead of needing to do





    WHERE dateField >= '2003-3-11 0:00:01' AND dateField <= '2003-4-11 23:59:59'





    you can more simply code





    WHERE dateField >= '2003-3-11' AND dateField < '2003-4-12'





    Notice the operators: >= but just <





    And notice that I bumped the date for the end date to the *next* day. Because *any* time on the prior date will be *less than* that value.





Comments have been disabled for this content.