I'm working on an ASP.Net application with VB and a SQL Server database. The app has some date fields which need to be searchable. The problem is the user might only enter parts of the date or the dates can be in different formats while inputting or searching the database. The user could enter only the Year, the Month/Year, or the Month/Day/Year when storing and searching the data.
The database stores the date as
B October 01
C Oct 21, 2001
When the user searches by a date range,
They can enter 2001 and the results would be: A, B, C, & D
They can enter Oct 2001 and the results would be: B, C, & D
They can enter a specific date 10/21/01 and the results would be: C
The can enter a range Jan 01 thru Jan 02 and the result would be: B, C, & D
The users have indicated that they want a text boxes instead of separate drop downs for the Month, Day & Year.
How could something like this be accomplished? Would the SQL Server data type be DateTime? Would the dates have to be reformatted to help with the consistancy? Any example code would be greatly appreciated!!