Post reply

Unknown Number of Multiple Date Ranges in WHERE Clause

  • January 19, 2005 at 6:51 am

    #65334

    Application with calander control permits selection of multiple ranges of dates (using ctrl key) which are then needed in a where clause to filter/synch a grid of data displayed to include the dates selected.

    What T-SQL strategies can be used to accomplish this request and can they be encapsulated into a callable mechanism (e.g. stored procedure, UDF)?

    Example: User selects 01/04/2005, 01/06/2005, 01/08/2005 - 01/11/2005 in calander control so the WHERE clause needs something like WHERE DatabaseDatetime ....

    THANKS for any insights on this problem!

  • January 19, 2005 at 7:10 am

    #538055

    Presumably you are writing a procedure to do this so the answer will depend on how you pass the dates.

    If you pass them as a single delimited string then two options spring to mind

    use CHARINDEX to find DatabaseDatetime in the supplied string making sure both dates are formatted correctly, ie yyyymmdd

    or create a UDF (there are samples of this type of UDF on this site) that takes the string and creates a table that you can join to the source table.

    the join method is probably faster depending on any indexing on the table being search as the CHARINDEX option will have to read each row (table or index scan)

    Far away is close at hand in the images of elsewhere.
    Anon.

  • January 21, 2005 at 6:40 am

    #538499

    THANKS for the ideas...

    I am still stuck on how the WHERE clause would be constructed. The requirement is that these date ranges may include single dates and actual from/thru ranges.

    Additionally this will be generated from the application (WinForm in VB.NET) and I'd like some ideas for the best way to send the information from the calander control to a table for usage in filtering the results. Do I create one string with the results and parse them via t-sql into the table to be used as an EXISTS within the main queries' WHERE clause?

    Any addtional thoughts or ideas would be appreciated!

  • January 21, 2005 at 6:52 am

    #538501

    First, get the client app to convert the selected dates and ranges (formatted) into a single string eg yyyymmdd,yyyymmdd,yyyymmdd etc

    If performance is acceptable (depending on size of table, indexes etc) then

    WHERE CHARINDEX ( ',' + CONVERT(char(8),[date],112) + ',' , ',' + @dateparam + ',') > 0

    Otherwise create a temp table, insert each date from the string and then join the temp table to find matching dates

     

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply