[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

Search Date with Different Formats

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.  

Example ...

The database stores the date as

A  2001
B  October 01
C  Oct 21, 2001
D  10/27/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!!

  • 6
  • 5
  • 3
1 Solution
Well, I can help you with the easy part!  Save the dates as datetime in SQL server - that way you can use the date functions in your queries.

Probably the easiest way to do what you want would be a few pop up calendars that the users could pick dates from.

If you absolutely have to have a google like text field, then you'll need to write (or find) a fairly sophisticated text parser.
The goal of the parser would be to identify what was entered, and map it to the sql commands needed to return the results.

for example, you'd read the string one char at a time, until you had a space. then you'd look at what you had - is it 4 numbers? are the first 2 = 19 or 20, if so you probably have a year.
if there's nothing else in that string, your query would be Select * from myDB where mydate between '1/1/'+year and '12/31/'+year

It gets more complicated as you try and add more possiblilities.

One approach you might employ to keep it manageable, is to have multiple blanks on your form...


       YEAR _________  [Submit]

       Mo/Year _______ [Submit]
       Date _________  [Submit]

       Range ________  to ________ (enter full dates ie 1/1/2005) [Submit]
nmachinAuthor Commented:
If you save the data as DateTime in the SQL Server, won't the database autofill the day and month as Jan 1?  If so, that will make the app display an invalid Month/Day in other areas.

The 'Google Like Text Field' is what we're looking for.  To avoid confussion, the users want to keep the form as simple as possible with as few of places to type as possible.  
I don't know anything about your app so it's hard to say what the impact will be of choosing date time vs something else.

However, you outlined one requirement that dictates that you store a complete date - the need to choose date ranges.
you could store a date as a string, with the most significant field first
IE 20060620 would represent today., then you could still use between searches.
I'm still torn on your need for different data types though.
What is a month? is it one day, is it all of them?
for example if someone wants June, do they want 6/1->6/30 ?

if they want May 15-> June 15 - what records do they want? do they want june records without a date (ie 20060600?) if so wouldn't they also expect May records without a date?

You see the problem here...

Ok I thought of one more possibility...
Create a text field.
parse out in advance, all your date data, and insert keywords into the text field.
For example if you have a field now that has the value 10/1/2006, insert the values 10 1 2006 OCT into your text field

Now you can use that text field with the like operator to find partial matches.

OCT 2006 [Submit]
becomes select realdate from table where textdate like OCT and textdate LIKE 2006
(I know my syntax is off on the like statement, but you get the idea)
The only thing you couldn't do with the above is ranges

Would that make things easier?

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

You can parse the string from frontend, using DateTime datatype to get year, month, day out. If user has to use free text, they have to pre-define some format, and you can handle these formats by anticipating what they could be.

At the same time, you can use the SQL Server DATEPART() function to match with the front end.

Hope this helps.
nmachinAuthor Commented:
Probably 99% of the searches will be off either Year or Month/Year and almost all searchs will be off a range.  If somebody wants June, it will search 6/1 -> 6/30.

I didn't even think about searching the records without a date so if the user wants 15 May - 15 Jun, they should expect all of May and June's records.

The extra string field with Keywords sounds like it might be the way to go if it could do ranges.
nmachinAuthor Commented:

the problem is anticipating the format in which the user types in the dates.  If thousands of searchs are done, I'm sure about every way imaginable would be used.

I'm not very familiar with the SQL Server DatePart() function.  Could it be used to break down a string for keywords in a stored proc?
To use range, the datatype has to be numeric, not string. You can convert front end user input to any numeric data type by using DateTime's function or property, eg, using DateTime.Year convert to year(vairable1). Then you parse out your database date column by using DatePart(yyyy, columnname) to a new variable(variable 2). Finally,  you use this  variable1 to query against your database's variable2 by using any greater than or smaller than queries.
nmachinAuthor Commented:

Do you have a coding example of how to pull a 'year' or 'month' out of a textbox in asp.net using the DateTime function?  My attempts are resulting in errors.
I'm doing this from the top of my head so syntax might be off...
dim x as date
dim day as int
dim month as int
dim year as int

x = textbox1.text
day = x.day
month = x.month
year = x.year
Just tested, and between works fine for strings formatted as YYYYMMDD

So if you can keep your dates in that format, you could get away with it
Just remember - if you only have a year you'd need to store it like 20060000
In SQL you'd use a between query
ie select * from myfield where mytextdate between '20060601' and '20060615'  (you need the quotes because they are text)

then it just boils down to parsing the strings and constructing the queries.

Also, you might consider a 'layered' search approach.
You could have a few methods of storing the data, and a few methods of returning results - with the results all merging together before the user sees them.

Again I think it all comes down to the parser that you write.
this would seem a fairly common task, so maybe this work has already been done?

Try www.codeproject.com - enter date parser in the search box.
Sample Code in C#

SELECT * from event
WHERE  (DATEPART(yyyy, yourdatecolumn) >= @startYear)

You need to parse out the Year from your text box, which depends on your string format. For example,

DateTime dt = DateTime.ParseExact("07302006","MMddyyyy", frmt);
int year = dt.Year;

year is then passed in as parameters for @startYear

The End. :)

nmachinAuthor Commented:
Thanks for your help xpy1999 but I'm going to go with fruhj's answer.   Fruhj, it looks like your code will accept most of the day formats so the date parser shouldn't be to complicated.

Let me know if you have questions along the way...

- Jack

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now