Using the WHERE clause with INTERNATIONAL and null date parameters

Question

One of my WHERE clauses is as follows:

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

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

If they are both NULL, I need to ignore BETWEEN and return all records.

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

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

DateCreated is not null or for a while it is a null field.

So far, my WHERE is not working the way I want.

Answer 1

Additional criteria are just needed for processing 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 problem with BETWEEN or <= when using the DATE variable in the DATETIME field is that any time after midnight on the last day will be excluded.

'2015-02-11 13:07:56.017' more '2015-02-11' Instead of distinguishing your field as DATE for comparison, it is better for performance to add a day to your variable and change from <= to < .

Answer 2

Try the following:

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

Answer 3

What about:

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

Answer 4

Use 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 ) 

Answer 5

This can simply be done using the ISNULL function.

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

Answer 6

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