SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Searching for only part of a date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

whsbeernuts
Starting Member

USA
11 Posts

Posted - 10/05/2007 :  14:01:43  Show Profile  Send whsbeernuts an AOL message  Reply with Quote
Ok, so I have a Select statement that looks like this:

Select * from FilteredOpportunity
where (OpportunityID LIKE @opportunityid)
and (Name LIKE @name)
and (CustomerIDName LIKE @customeridname)
and isNull(EstimatedCloseDate, '1900-01-01') LIKE @estimatedclosedate
and isNull(Description, '') LIKE @description
and isNull(AccountIdName, '') LIKE @AccountIDName
and isNull(ACCU_OppTypeName, '') LIKE @ACCU_OppTypeName
and isNull(SalesStageCodeName, '') LIKE @SalesStageCodeName
and isNull(ACCU_CustomerTypeName, '') LIKE @ACCU_CustomerTypeName
and (CreatedByName LIKE @CreatedByName)
and (CreatedOn LIKE @CreatedOn)
and isNull(ACCU_OppSourceName, '') LIKE @ACCU_OppSourceName
and (StateCodeName LIKE @StateCodeName)
and (StatusCodeName LIKE @StatusCodeName)

This chunk of code is used in an XML file which, once this XML is imported (using sharepoint by the way if you haven't noticed), I can then search through tables with different filters. Well, my date filter isn't working the same way as my other ones. I have a couple different date filters, but none of them work.

Before I ask my question, I only pass in one variable at a time through my code.... the rest of the variables get a default value passed to them if the user doesn't pass it. That value is simply '%'. Anyways, so the different ways a user can type in a date is by typing in the whole date, part of the date starting with the beginning of it, typing in the end of the date, or typing something within the date. For a string it's like 'Example', 'Exam%', '%ple', and '%amp% respectively. How do I accomplish this with a date?

Kristen
Test

United Kingdom
22403 Posts

Posted - 10/06/2007 :  01:42:17  Show Profile  Reply with Quote
Its an unusual way to filter a date. Do users really want to be able to filter on, say, there being an "R" in the Month name, for example?

Or are they looking for a date WITHIN a Range?

Or for all records for a specific Month or Year or Both?

Or even a specific day?

Because a LIKE '%01%' is going to find 1st of the month, January and 2001 - which seems unlikely to be useful.

If they want a specific day / month / year give them three boxes to fill in.

If they want a Range give them start/end boxes.

Then pass those parameters.

Please note that your LIKE query using the style

and (Name LIKE @name)

for all parameters (and providing a parameter of "%" for the "Don't Care" parameters will work fine for string/text datatypes, but it is very inefficient.

This may not matter if your database is small and the server is not heavily used, but if this is for an application which will grow much larger / have lots of users then this approach is likely to be too slow in the long term.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000