如何在BETWEEN子句中包含空值?

Question

在我的查询的where子句中，我有条件:

User.DateOfBirth BETWEEN @startDate AND @endDate 

其中@startDate和@endDate为空。如果@startDate为空，我希望所有值小于或等于@endDate;如果@endDate为空，我想要所有大于或等于@startDate的值;如果两者都为空，则需要所有值

我的尝试失败-返回0个结果

((( User.DateOfBirth > @startDate) OR (@startDate Is null)) AND  (( User.DateOfBirth < @endDate) OR (@endDate is null)) )

(编者注:BETWEEN包含结束点(小于/大于或等于)，但对空情况的描述没有。

Answer 1

试试这个:

[User].DateOfBirth BETWEEN ISNULL(@startDate,[User].DateOfBirth) AND ISNULL(@endDate,[User].DateOfBirth)

Answer 2

我想到了两种方法:

分别处理四种情况，然后将它们放在一起或:

1. 起始和结束为空:任何日期匹配，
2. start为null，所以需要DoB <= end
3. send为null，所以需要DoB>= start
4. 都不是null，所以需要

这将导致一个长表达式。

使用IsNull:

正如mehdi lotfi在他的回答中所示。

Answer 3

如果您使用的是可空日期时间类型，则可以使用

User.DateOfBirth BETWEEN isnull(@startDAte, CAST('1753-01-01' AS datetime)) AND isnull(@endDAte, CAST('9999-12-31' AS datetime))

for datetime2 use

User.DateOfBirth BETWEEN isnull(@startDAte, CAST('0001-01-01' AS datetime2)) AND isnull(@endDAte, CAST('9999-12-31' AS datetime2))

Answer 4

WHERE
((User.DateOfBirth BETWEEN @startDAte AND @endDAte) AND @startDAte is not null AND @endDAte is not null)
OR
((User.DateOfBirth =< @endDAte) AND @startDAte is null AND @endDAte is not null)
OR 
 ((User.DateOfBirth >= @startDAte) AND @startDAte is not null AND @endDAte is  null)
OR 
(1=1 AND @startDAte is null AND  @endDAte is  null)

Answer 5

试试这个:

select * from yourtable
where 
(@startdate is null and @enddate is null)
or
(@startdate is null and @enddate is not null and User.DateOfBirth <= @enddate)
or
(@startdate is not null and @enddate is null and User.DateOfBirth >= @startdate)
or
(@startdate is not null and @enddate is not null and User.DateOfBirth BETWEEN @startdate AND @enddate)

处理所有可能的情况:

• 两个参数都为null -返回所有结果
• 非空结束日期-返回DOB <=结束日期的所有结果
• 非空开始日期-返回DOB>=开始日期的所有结果
• 两个参数都是非空的-返回DOB在开始和结束日期之间的所有结果