SQL 将 WHERE 子句与 BETWEEN 和空日期参数一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28459783/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using WHERE clause with BETWEEN and null date parameters
提问by gooseman
One of my WHEREclauses is the following:
我的WHERE条款之一如下：
AND (DateCreated BETWEEN @DateFrom and @DateTo OR (@DateFrom IS NULL OR @DateTo IS NULL))
@DateFromand @DateToare input parameters that may be NULL.
@DateFrom和@DateTo是输入参数，可能是NULL。
If they are both null, then I need to basically ignore the BETWEENand return all records.
如果它们都为空，那么我需要基本上忽略BETWEEN并返回所有记录。
If @DateFromis NULL, but @DateTois 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 @DateFromis NOT NULL, but @DateTois 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 or some time it is null field.
DateCreated 不是 null 或有时它是 null 字段。
So far my WHEREclause is not working exactly like I want.
到目前为止，我的WHERE条款并没有像我想要的那样工作。
采纳答案by Hart CO
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 BETWEENor <=when using a DATEvariable against a DATETIMEfield, is that any time after midnight on the last day will be excluded.
这个问题BETWEEN或者<=使用时，DATE变量对一个DATETIME领域，是午夜的最后一天之后的任何时间将被排除在外。
'2015-02-11 13:07:56.017'is greater than '2015-02-11'Rather than casting your field as DATEfor 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为比较，最好将一天添加到您的变量并从 更改<=为<。
回答by Giorgi Nakeuri
Try this:
尝试这个：
WHERE ((DateCreated >= @DateFrom OR @DateFrom IS NULL) AND (DateCreated =< @DateTo OR @DateTo IS NULL))
回答by PRowLeR
How About:
怎么样：
DateCreated BETWEEN COALESCE(@DateFrom, DateCreated) AND COALESCE(@DateTo, DateCreated)
回答by P?????
Use this where clause
用这个 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 )
回答by Zulqarnain Ejaz
This can simply be done by using ISNULL function.
这可以简单地通过使用 ISNULL 函数来完成。
DateCreated BETWEEN ISNULL(@DateFrom,DateCreated) AND ISNULL(@DateTo,DateCreated)
回答by Chamara
AND (DateCreated BETWEEN @DateFrom and @DateTo OR (ISNULL(@DateFrom, '')='' OR ISNULL(@DateTo, '')=''))
