搜索多个日期过滤器的查询

I need to write a query for my search page. My filters look like this:

我需要为我的搜索页面写一个查询。我的过滤器是这样的:

@StartDateForColumn1 datetime,
@FinishDateForColumn1 datetime,
@StartDateForColumn2 datetime,
@FinishDateForColumn2 datetime,

All of them can be null. If so, I need all data:

它们都可以是空的。如果是，我需要所有的数据:

select * from Table1

For example: If only@FinishDateForColumn1 is filled by the user, the query will be:

例如:如果用户只填写@ finishdateforcolumn1，查询将是:

select * from Table1 where Column1<@FinishDateForColumn1

So I don't want to write if-else for all conditions. Is there any other shortest way to write a query for this example? Maybe I need to use coalesce, but I don't know how to use it in this query.

所以我不想在所有条件下都写if-else。是否有其他最短的方法来为这个例子编写查询?也许我需要使用coalesce，但是我不知道如何在这个查询中使用它。

4 个解决方案

#1

the following assumes that you don't have any dates outside the range 01/01/1971 and 31/12/2099.

以下假设您没有任何日期在01/01/1971和31/12/2099。

select * 
from Table1 
where Column1< ISNULL(@FinishDateForColumn1,'31/Dec/2099') 
and Column1 > ISNULL(@StartDateForColumn1, '01/Jan/1971')
and Column2< ISNULL(@FinishDateForColumn2,'31/Dec/2099') 
and Column2 > ISNULL(@StartDateForColumn2, '01/Jan/1971')

#2

You can use coalesce, or isnull, in the following fashion

您可以按照以下方式使用coalesce或isnull

select * 
from Table1 
where Column1<= isnull(@FinishDateForColumn1, Column1)

NB. This does assume that your column1 data does not contain nulls.

NB。这确实假设您的column1数据不包含null。

#3

You can do the following, which essentially ignores each parameter if it's null (by forcing the predicate criteria to be true) or uses it in the comparison if it is present.

您可以执行以下操作，如果每个参数为null(通过强制谓词条件为true)，它实质上会忽略它;如果存在，则在比较中使用它。

select *
from Table 1
where
  Column1 < case when @StartDateForColumn1 is null then Column1 + 1 else @StartDateForColumn1 end
  and Column1 < case when @FinishDateForColumn1 is null then Column1 + 1 else @FinishDateForColumn1 end
  and ...

#4

Something simple like using ISNULL would probably suffice.

使用ISNULL之类的简单方法可能就足够了。

WHERE 
    Column1 BETWEEN 
        ISNULL(@StartDateForColumn1, CAST('1 Jan 1753' as DATETIME)) 
        AND
        ISNULL(@EndDateForColumn1, CAST('1 Jan 9999' as DATETIME))
    AND
        Column2 BETWEEN 
            ISNULL(@StartDateForColumn2, CAST('1 Jan 1753' as DATETIME)) 
            AND
            ISNULL(@EndDateForColumn2, CAST('1 Jan 9999' as DATETIME))

#1