Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Searching for only part of a date

Author  Topic 

whsbeernuts
Starting Member

11 Posts

Posted - 2007-10-05 : 14:01:43
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

22859 Posts

Posted - 2007-10-06 : 01:42:17
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
   

- Advertisement -