We help IT Professionals succeed at work.

Search Date with Different Formats

nmachin
nmachin asked
on
288 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!!


Comment
Watch Question

Commented:
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]

Author

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.  

Commented:
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?

Commented:
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.

Author

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.

Author

Commented:
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?

Commented:
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.

Author

Commented:
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.
 

Commented:
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
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:

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

Commented:
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. :)






Author

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.

Commented:
Cool!

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

- Jack

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.