Solved

Search Date with Different Formats

Posted on 2006-06-20
14
255 Views
Last Modified: 2008-03-17
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!!


0
Comment
Question by:nmachin
  • 6
  • 5
  • 3
14 Comments
 
LVL 12

Expert Comment

by:fruhj
ID: 16942864
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...

 ie:

  SEARCH FORM
   PICK ONE
       YEAR _________  [Submit]

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

       Range ________  to ________ (enter full dates ie 1/1/2005) [Submit]
0
 

Author Comment

by:nmachin
ID: 16943070
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.  
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16943245
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.

IE
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?

0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

Expert Comment

by:xpy1999
ID: 16943702
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.
0
 

Author Comment

by:nmachin
ID: 16943841
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.
0
 

Author Comment

by:nmachin
ID: 16943977
xpy1999,

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?
0
 

Expert Comment

by:xpy1999
ID: 16944552
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.
0
 

Author Comment

by:nmachin
ID: 16944822
xpy1999,

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.
 
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16945328
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
0
 
LVL 12

Accepted Solution

by:
fruhj earned 500 total points
ID: 16945426
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?
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16945511

Try www.codeproject.com - enter date parser in the search box.
0
 

Expert Comment

by:xpy1999
ID: 16945617
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. :)






0
 

Author Comment

by:nmachin
ID: 16950605
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.
0
 
LVL 12

Expert Comment

by:fruhj
ID: 16952753
Cool!

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

- Jack
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now