在SQL CASE where子句中使用BETWEEN

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。如果他们选择null，则返回所有记录，其中effectivedate （）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

5 个解决方案

#1

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

#2

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

#3

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

#4

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)

#5

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不会有条件地选择评估的代码。您可以将大小写视为一个值，因此您必须在大小写和另一个操作数之间放置一个运算符。

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.

如果您不想用大写字母书写它，您可以选择其中一个解决方案，但它们使用离散的开始日期。

#1