A single sql query which can handle both null or valued date range in sql server

Question

Using SQL Server 2008. I have a stored proc which has start and end date as input parameters for date range.

Looking for a single sql query which has a between start and end date in the where clause which can handle both cases where the dates are either both null or both have values.

I don't want to use an IF statement.

Solution 1

WITH    limits AS
        (
        SELECT  COALESCE(@startDate, MIN(mydate)) AS startDate, COALESCE(@endDate, MAX(mydate)) AS endDate
        FROM    mytable
        )
SELECT  m.*
FROM    limits
JOIN    mytable m
ON      mydate BETWEEN startDate AND endDate

This will be most efficient if there is an index on mydate, since this condition is sargable and will use an Index Seek.

If there is no index, then use IFNULL constructs proposed by others.

Solution 2

You can do this:

SELECT blah
FROM MyTable
WHERE 
    (@startDate IS NULL OR MyTable.StartDate >= @startDate)
    AND (@endDate IS NULL OR MyTable.EndDate <= @endDate)

But please be aware that a large number of parameters in AND clauses like this can lead to incorrectly cached query plans. There are many questions on SO about incorrect query plans and parameter 'sniffing'.

Solution 3

Quassnoi's answer is probably best but here's another take:

SELECT *
FROM MyTable
WHERE 
    MyTable.StartDate >= ISNULL(@startDate, MyTable.StartDate)
    AND MyTable.EndDate <= ISNULL(@startDate, MyTable.EndDate)

Solution 4

SELECT *
FROM MyTable
WHERE 
     MyTable.StartDate >= COALESCE(MyTable.StartDate, "1/1/1900") 
     /* Date selected as earliest plausible constant to avoid min() lookup */

 AND MyTable.EndDate <= COALESCE(MyTable.EndDate, "1/1/3001")
     /* Date selected as latest plausible constant to avoid max() lookup */

You need to select correct constants for your app/domain, obviously. It's a wee bit risky if you don't have constants wide enough but a lot faster than explicitly looking min/max up from the table, and most apps/domains have pretty well defined frames.

Solution 5

SELECT
    Column1,....
    FROM MyTable
    WHERE MyTable.StartDate>=COALESCE(@startDate,CONVERT(datetime,'01/01/1753'))
        AND MyTable.EndDate<=COALESCE(@endDate,CONVERT(datetime,'12/31/9999'))

also, here is a very comprehensive article on this topic:

Dynamic Search Conditions in T-SQL by Erland Sommarskog

it covers all the issues and methods of trying to write queries with multiple optional search conditions

here is the table of contents:

   Introduction
      The Case Study: Searching Orders
      The Northgale Database
   Dynamic SQL
      Introduction
      Using sp_executesql
      Using the CLR
      Using EXEC()
      When Caching Is Not Really What You Want
   Static SQL
      Introduction
      x = @x OR @x IS NULL
      Using IF statements
      Umachandar's Bag of Tricks
      Using Temp Tables
      x = @x AND @x IS NOT NULL
      Handling Complex Conditions
   Hybrid Solutions – Using both Static and Dynamic SQL
      Using Views
      Using Inline Table Functions
   Conclusion
   Feedback and Acknowledgements
   Revision History

View more solutions

Comments

[dnagirl]

Nice. I like the limiting of the date range to what's already in the table.

[DVK]

Be careful: The min/max() calls are expensive in absence of good date index (which the user didn't explicitly specify)

[B Bulfin]

Beware this prevents index use.

[wcm]

Much as I hate to vote against myself, this seems to be the best, assuming an index exists. +1

[wcm]

I think this is slightly easier to read but I think recursive is correct.

[Dineshkumar]

wcm - you aren't voting against yourself so much as helping to push the right answer across the finish line. Truly, points don't really matter too much and you'll be happier on SO the less you worry about them.

[Dineshkumar]

+1 - helpful but recursive makes a good point regarding the index.