Using WHERE clause with BETWEEN and null date parameters

Question

One of my WHERE clauses is the following:

AND (DateCreated BETWEEN @DateFrom and @DateTo OR (@DateFrom IS NULL OR @DateTo IS NULL))

@DateFrom and @DateTo are input parameters that may be NULL.

If they are both null, then I need to basically ignore the BETWEEN and return all records.

If @DateFrom is NULL, but @DateTo is NOT NULL, then I need to return all records with DateCreated being no greater than @DateTo (inclusive).

If @DateFrom is NOT NULL, but @DateTo is NULL, then I need to return all records with DateCreated being no earlier than @DateFrom (inclusive) up to today's date.

DateCreated is not a null or some time it is null field.

So far my WHERE clause is not working exactly like I want.

Assuming both the column and parameters are DATE, then WHERE DateCreated >= COALESCE(@DateTo, '19000101') AND DateCreated <= COALESCE(@DateFrom, CONVERT(DATE, GETDATE())); - if either of them are DATETIME, you need to stop thinking about "between"... — Aaron Bertrand
– Aaron Bertrand, Commented Feb 11, 2015 at 16:53
I think it would be worth reading What do BETWEEN and the devil have in common?. Although not incorrect I agree with the article in that BETWEEN causes more problems with ambiguity than it solves by shortening a predicate slightly. — GarethD
– GarethD, Commented Feb 11, 2015 at 16:54
@Matt I disagree 100%. If the parameters are not null, and there is a usable index on DateCreated, it will be used. I define "usable" there loosely - needs to cover the query or not introduce lookups that are too costly. But the predicate I wrote is definitely sargable. If your parameters are NULL you're probably doing a full scan anyway. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 11, 2015 at 16:56
@Matt Please read the code a little more carefully. Those are happening against the parameters, not against the column. Any proper RDBMS would know to only perform that operation once. — Aaron Bertrand
– Aaron Bertrand, Commented Feb 11, 2015 at 16:59
@Matt - this would be true if a column value was contained inside the COALESCE or CONVERT functions. The code is just COALESCE(@DateFrom, CONVERT(DATE, GETDATE())) - Both the parameter @DateFrom and the system function GETDATE() are runtime constants, therefore COALESCE and CONVERT will only be evaluated once. — GarethD
– GarethD, Commented Feb 11, 2015 at 17:01

Hart CO · Accepted Answer · 2015-02-11 18:11:23Z

18

Just need some extra criteria to handle when one or the other is NULL:

AND (
    (DateCreated >= @DateFrom and DateCreated < DATEADD(day,1,@DateTo)) 
 OR (@DateFrom IS NULL AND @DateTo IS NULL)
 OR (@DateFrom IS NULL AND DateCreated < DATEADD(day,1,@DateTo))
 OR (@DateTo IS NULL AND DateCreated >= @DateFrom)
    )

Edit: Giorgi's approach was simpler, here it is adapted for use with DATETIME:

AND (       (DateCreated >= @DateFrom OR @DateFrom IS NULL) 
        AND (DateCreated < DATEADD(day,1,@DateTo) OR @DateTo IS NULL)
    )

The issue with BETWEEN or <= when using a DATE variable against a DATETIME field, is that any time after midnight on the last day will be excluded.

'2015-02-11 13:07:56.017' is greater than '2015-02-11' Rather than casting your field as DATE for comparison, it's better for performance to add a day to your variable and change from <= to <.

edited Feb 11, 2015 at 18:11

answered Feb 11, 2015 at 16:49

Hart CO

34.9k6 gold badges52 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hart CO Over a year ago

@NoDisplayName Always a race!

gooseman Over a year ago

This is the closest correct answer for me. But still have an issue. It doesn't seem to be working inclusively. The DateCreated field is datetime, but the input parameters @DateFrom and @DateTo are date. Could that have something to do with this issue?

Hart CO Over a year ago

@leo.t Definitely! Since date has no time component, a datetime on the same date for anything after midnight will not be included on the last day of your range, updated.

Petar Petrov Over a year ago

It's worth mention that there's a problem if @DateTo has already the max value for a DATETIME(December 31, 9999). DATEADD will return an error - "Adding a value to a 'datetime' column caused an overflow."

Giorgi Nakeuri · Accepted Answer · 2015-02-11 16:50:57Z

18

Try this:

WHERE ((DateCreated >= @DateFrom OR @DateFrom IS NULL) AND (DateCreated =< @DateTo OR @DateTo IS NULL))

answered Feb 11, 2015 at 16:50

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

4 Comments

Matt Over a year ago

Surprisingly, this logic defeats the indexing on DateCreated. I discovered this through testing to confirm my losing argument against @AaronBertrand above

GarethD Over a year ago

@Matt if you run the same query with the OPTION (RECOMPILE) query hint you should find that it uses an index on CreatedDate - the reason you won't see it used as standard is that (assuming you have run this ad hoc with declared parameters) the query is compiled with in a "one plan fits all" manner. If the plan uses an index seek and a key lookup then when run with both parameters null the performance will be awful, if the plan uses a table scan then when run with both dates not null to return a small number of rows the performance will still be bad, but not as bad.

Matt Over a year ago

@GarethD, assertions confirmed. Awful with nulls, great without. I'm gonna stop trying to be helpful now!

baumannalexj Over a year ago

what about coalesce like in @prowler 's answer stackoverflow.com/a/41974680/6791342, does that beat indexing?

PRowLeR · Accepted Answer · 2017-02-01 08:04:34Z

10

How About:

DateCreated BETWEEN COALESCE(@DateFrom, DateCreated) AND COALESCE(@DateTo, DateCreated)

answered Feb 1, 2017 at 8:04

PRowLeR

3024 silver badges13 bronze badges

Comments

Pரதீப் · Accepted Answer · 2015-02-11 16:49:11Z

6

Use this where clause

WHERE  ( DateCreated BETWEEN @DateFrom AND @DateTo )
        OR ( @DateFrom IS NULL
             AND @DateTo IS NULL )
        OR ( @DateFrom IS NULL
             AND DateCreated <= @DateTo )
        OR ( @DateTo IS NULL
             AND DateCreated >= @DateFrom )

answered Feb 11, 2015 at 16:49

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

Zulqarnain Ejaz · Accepted Answer · 2019-05-16 13:26:14Z

3

This can simply be done by using ISNULL function.

DateCreated BETWEEN ISNULL(@DateFrom,DateCreated) AND ISNULL(@DateTo,DateCreated)

answered May 16, 2019 at 13:26

Zulqarnain Ejaz

3341 gold badge2 silver badges10 bronze badges

1 Comment

Philippe Cloutier Over a year ago

ISNULL is not identical to COALESCE, but in this case, PRowLeR's answer would be better since COALESCE is standardized by SQL: mssqltips.com/sqlservertip/2689/…

naXa stands with Ukraine · Accepted Answer · 2018-12-29 01:26:56Z

0

AND (DateCreated BETWEEN @DateFrom and @DateTo OR (ISNULL(@DateFrom, '')='' OR ISNULL(@DateTo, '')=''))

edited Dec 29, 2018 at 1:26

naXa stands with Ukraine

38.7k25 gold badges208 silver badges279 bronze badges

answered Mar 29, 2017 at 3:33

Chamara

2093 silver badges3 bronze badges

1 Comment

Robert Columbia Over a year ago

While this code may answer the question, it is better to explain how to solve the problem and provide the code as an example or reference. Code-only answers can be confusing and lack context.

Collectives™ on Stack Overflow

Using WHERE clause with BETWEEN and null date parameters

6 Answers 6

4 Comments

4 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

4 Comments

Comments

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related