|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Date between two wildcardsI have a webapplication where the administrator can view into a
logbook. To shorten the list of the logbook, he can filter the list by entering a startdate AND/OR an enddate. If he doesn't fill in the dates, the list shouldn't be filtered. So this is what I did for my PreparedStatement SELECT * from logbook WHERE date >= ? AND date <= ? If the user fills in nothing, I gave the field ? => "%" as wilcard. That works perfect for >= but not for <=. So my question is: how to make the query right if the user specifies no startdate and no enddate? ( yes, I could use different sql queries, but I just want one! ) Use the minimum and the maximum allowed values:
SELECT * from logbook WHERE (date >= '1750-01-01 00:00:00.000') AND (date <= '9999-12-31 23:59:59.997') Are you certain that your current conditions ('%') return expected results? ML --- http://milambda.blogspot.com/ Yes, they work for ">=", but not otherwise.
But your solution works also, but I don't know if it's really the "way to go" :-) ML schreef: Show quoteHide quote > Are you certain that your current conditions ('%') return expected results? You could also try:
where (<date column> >= @dateParam or @dateParam is null) and (<date column> <= @dateParam of @dateParam is null) ....however, the optimizer will most probably end up using a table scan in this case. Your best alternative would be to not use the conditions at all when the @dateParam is not specified. Perhaps you should do more reading on this subject: http://www.sommarskog.se/dyn-search.html ML --- http://milambda.blogspot.com/ ML (M*@discussions.microsoft.com) writes:
> Use the minimum and the maximum allowed values: Small correction: the minimum date is 17530101> > SELECT * > from logbook > WHERE (date >= '1750-01-01 00:00:00.000') > AND (date <= '9999-12-31 23:59:59.997') > Are you certain that your current conditions ('%') return expected Apparently the client API converts % to some date in the past. SQL Server> results? does not seem to like it. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Small correction: the minimum date is 17530101 Oh, yes, right. A typo. Thanks for pointing it out.ML --- http://milambda.blogspot.com/ <jochenhebbre***@gmail.com> wrote in message
Show quoteHide quote news:1157363415.292279.218240@74g2000cwt.googlegroups.com... do it clientside ?>I have a webapplication where the administrator can view into a > logbook. To shorten the list of the logbook, he can filter the list by > entering a startdate AND/OR an enddate. If he doesn't fill in the > dates, the list shouldn't be filtered. > > So this is what I did for my PreparedStatement > > SELECT * from logbook WHERE date >= ? AND date <= ? > > If the user fills in nothing, I gave the field ? => "%" as wilcard. > That works perfect for >= but not for <=. > > So my question is: how to make the query right if the user specifies no > startdate and no enddate? > > ( yes, I could use different sql queries, but I just want one! ) if to date is blank then date is 01/01/2005 if from date is blank then date is 01/01/1980 or something then just between @fromdate and @todate mark  
Other interesting topics
import 1 mln records = problem
Trigger When Row Changes Add a Column at Particullar Position n MS SQL Server Query question SQL server 2005 query problem Problem updating large number of records Doing JOIN with multiple table on multiple fields in each table concatenate column from rows Record searching question How to list all indexs from a table? |
|||||||||||||||||||||||
