CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
Tony_Henrich
Tony_Henrich

Reputation: 44085

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

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.

Upvotes: 7

Views: 12132

Answers (7)

vaibhav
vaibhav

Reputation: 1017

You can do this

SELECT blah
FROM MyTable
WHERE 
1 = case 
        when @startDate IS NOT NULL then  MyTable.Date >= @startDate
    else 1 end
AND
1 = case 
        when @endDate IS NOT NULL then  MyTable.Date <= @endDate
    else 1 end

or 

SELECT blah
FROM MyTable
WHERE 
(
    (@startDate is not null and @endDate is not null and MyTable.Date between @startDate and @endDate )
    or
    (@startDate is null and @endDate is null )
)

Upvotes: 1

Benjamin Short
Benjamin Short

Reputation: 1

For max value: 

Case when @a > @b or @b is null then @a else @b end.

This handles nulls as well.

Simple.

Upvotes: 0

KM.
KM.

Reputation: 103579

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

Upvotes: 1

wcm
wcm

Reputation: 9281

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)

Upvotes: 4

Quassnoi
Quassnoi

Reputation: 425271

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.

Upvotes: 12

DVK
DVK

Reputation: 129373

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.

Upvotes: 2

Mitch Wheat
Mitch Wheat

Reputation: 300519

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'.

Upvotes: 11

Related Questions