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.
| 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 @estimatedclosedateand 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 styleand (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 |
 |
|
|
|
|
|
|
|
Searching for only part of a date