将WHERE子句与BETWEEN和null日期参数一起使用

时间:2022-07-06 19:23:51

One of my WHERE clauses is the following:

我的一个WHERE子句如下:

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

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

@DateFrom和@DateTo是可能为NULL的输入参数。

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

如果它们都是null,那么我需要基本上忽略BETWEEN并返回所有记录。

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

如果@DateFrom为NULL,但@DateTo为NOT NULL,那么我需要返回DateCreated不大于@DateTo(包括)的所有记录。

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.

如果@DateFrom为NOT NULL,但@DateTo为NULL,那么我需要返回所有记录,其中DateCreated不早于@DateFrom(包括),直到今天的日期。

DateCreated is not a null field.

DateCreated不是空字段。

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

到目前为止,我的WHERE子句并不像我想的那样工作。

5 个解决方案

#1


4  

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

当一个或另一个为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:

编辑:Giorgi的方法更简单,在这里适用于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.

BETWEEN或<=在DATETIME字段中使用DATE变量时的问题是,将排除在最后一天午夜之后的任何时间。

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

'2015-02-11 13:07:56.017'大于'2015-02-11'而不是将您的字段作为DATE进行比较,而不是将性能添加到变量并从<=更改为< 。

#2


5  

Try this:

尝试这个:

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

#3


2  

How About:

怎么样:

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

#4


1  

Use this where clause

使用此where子句

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 ) 

#5


0  

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

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

#1


4  

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

当一个或另一个为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:

编辑:Giorgi的方法更简单,在这里适用于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.

BETWEEN或<=在DATETIME字段中使用DATE变量时的问题是,将排除在最后一天午夜之后的任何时间。

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

'2015-02-11 13:07:56.017'大于'2015-02-11'而不是将您的字段作为DATE进行比较,而不是将性能添加到变量并从<=更改为< 。

#2


5  

Try this:

尝试这个:

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

#3


2  

How About:

怎么样:

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

#4


1  

Use this where clause

使用此where子句

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 ) 

#5


0  

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

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