Solved

Search Date with Different Formats

Posted on 2006-06-20
14
253 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Expert Comment

by:xpy1999
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:nmachin
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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

Expert Comment

by:xpy1999
Comment Utility
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
Comment Utility
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
Comment Utility
Cool!

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

- Jack
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this Article, I will provide a few tips in problem and solution manner. Opening an ASPX page in Visual studio 2003 is very slow. To make it fast, please do follow below steps:   Open the Solution/Project. Right click the ASPX file to b…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

9 Experts available now in Live!

Get 1:1 Help Now