Search Date with Different Formats

Posted on 2006-06-20
Medium Priority
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!!

Question by:nmachin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
LVL 12

Expert Comment

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


       YEAR _________  [Submit]

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

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

Author Comment

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

Expert Comment

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.

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?

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

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.

Author Comment

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.

Author Comment

ID: 16943977

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?

Expert Comment

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.

Author Comment

ID: 16944822

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

Expert Comment

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

Accepted Solution

fruhj earned 2000 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?
LVL 12

Expert Comment

ID: 16945511

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

Expert Comment

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


Author Comment

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

Expert Comment

ID: 16952753

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

- Jack

Featured Post

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

770 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