在 SQL CASE where 子句中使用 BETWEEN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5416695/
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 BETWEEN in SQL CASE where clause
提问by MeltdownZA
I Have a form, where the user pulls a report. In the form they can choose a start date and an end date, or pass through a null for both values. If they choose null, it returns all records where effectivedate < GETDATE() The CASE statement doesn't seem to like between, nor does it like '<' operators
我有一个表单，用户可以在其中提取报告。在表单中，他们可以选择开始日期和结束日期，或者为这两个值传递一个空值。如果他们选择 null，则返回所有有效日期 < GETDATE() 的记录 CASE 语句似乎不喜欢介于两者之间，也不喜欢 '<' 运算符
Here is my script
这是我的脚本
SELECT * FROM tbReport
WHERE
EffectiveDate
CASE
WHEN (@StartDate IS NOT NULL AND @EndDate IS NOT NULL)
THEN BETWEEN (@StartDate AND @EndDate)
ELSE
THEN < GETDATE()
END
回答by Andomar
You could rewrite it without a case, like:
您可以在没有案例的情况下重写它，例如：
SELECT *
FROM tbReport
WHERE (
@StartDate is not null
and
@EndDate is not null
and
EffectiveDate between @StartDate AND @EndDate
)
or
(
(
@StartDate is null
or
@EndDate is null
)
and
EffectiveDate < getdate()
)
回答by Neville Kuyt
Assuming this is SQL Server, you can simplify this by using isnull:
假设这是 SQL Server，您可以使用 isnull 来简化它：
select *
from tbReport
where EffectiveDate between isnull(@StartDate, '1 Jan 1990')
and isnull(@EndDate, getdate())
回答by SecretDeveloper
Something like this should work, I havent checked the syntax is correct though.
这样的事情应该可以工作，但我还没有检查语法是否正确。
SELECT * FROM tbReport
WHERE EffectiveDate < IsNull(@EndDate,GetDate())
AND EffectiveDate > IsNull(@StartDate,'01/01/1979')
回答by Dumitrescu Bogdan
the SQL statement is not written correct. Case will not conditionally chose the evaluated code. You can look to the case as a value, so you have to put an operator between the case and the other operand.
SQL 语句写不正确。Case 不会有条件地选择评估的代码。您可以将 case 视为一个值，因此您必须在 case 和另一个操作数之间放置一个运算符。
One of the multiple ways of writing this would be:
编写此内容的多种方法之一是：
where
case when @StartDate IS NOT NULL AND @EndDate IS NOT NULL and EffectiveDate BETWEEN (@StartDate AND @EndDate) then 1
case when (@StartDate IS NULL OR @EndDate IS NULL) and EffectiveDate <getdate() then 1
else 0 end = 1
If you do not want to write it with case you can chose one of the solutions before, but they use a discrete starting date.
如果您不想用案例来编写它，您可以选择之前的解决方案之一，但它们使用离散的开始日期。
回答by Murali Krishna
WHERE
(SR.RecCreateTS BETWEEN ( CASE WHEN @SubmittedBegining IS NOT NULL THEN @SubmittedBegining ELSE SR.RecCreateTS END )
AND ( CASE WHEN @SubmittedEnding IS NOT NULL THEN @SubmittedEnding ELSE SR.RecCreateTS END )
)
回答by Blorgbeard is out
Your syntax for CASE is all wrong here.. Try this instead:
你的 CASE 语法在这里都是错误的..试试这个：
SELECT * FROM tbReport
WHERE
(@StartDate is null and @EndDate is null and EffectiveDate < GetDate()) or
(@StartDate is not null and @EndDate is not null and
EffectiveDate >= @StartDate and EffectiveDate <= @EndDate)
