将 WHERE 子句与 BETWEEN 和空日期参数一起使用 [英] Using WHERE clause with BETWEEN and null date parameters

问题描述

我的一个 WHERE 子句如下:

One of my WHERE clauses is the following:

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

@DateFrom 和 @DateTo 是输入参数，可能是 NULL.

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

如果它们都为空，那么我需要基本上忽略BETWEEN并返回所有记录.

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

如果 @DateFrom 是 NULL，但 @DateTo 是 NOT NULL，那么我需要返回所有DateCreated 不大于 @DateTo(含)的记录.

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 是 NOT NULL，但是 @DateTo 是 NULL，那么我需要返回所有DateCreated 不早于 @DateFrom(包括)到今天日期的记录.

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 不是 null 或有时它是 null 字段.

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

到目前为止，我的 WHERE 子句并没有像我想要的那样工作.

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

推荐答案

只需要一些额外的标准来处理当一个或另一个是 NULL 时:

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

Giorgi 的方法更简单，这里适用于 DATETIME:

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

对 DATETIME 字段使用 DATE 变量时 BETWEEN 或 <= 的问题是最后一天午夜之后的任何时间都将被排除.

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' 大于 '2015-02-11' 而不是将您的字段转换为 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 <.

这篇关于将 WHERE 子句与 BETWEEN 和空日期参数一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！