的日期SQL返回null

问题描述：

select EL.Datetimestart, E.Fullname, VisaExpiryDate, E.EmployeeStatusCode 
from EmployeeLog as EL 
inner join Employee as E on E.Employeekey = EL.EmployeeKey 
where DateTimeStart >= Getdate() -1 and VisaExpiryDate <=GetDate() and E.EmployeeStatusCode <> 'x' 
order by VisaExpiryDate

目前，这将返回谁拥有签证过期，并在这一天的工作的员工，

VisaExpiryDate - 2017-08-08 00:00:00.000 
DateTimeStart - 2017-08-10 08:00:03.090 
FullName - Rypsuke 
EmployeeStatusCode - C

签证信息检查并插入到系统中，这可能需要几天时间，因此新员工将空白字段留空。

我该如何回复这些空值以查看谁还没有签证检查和签证过期的人？

感谢您的帮助。如果我理解正确尼克

一个'NULL “作为什么领域？ –

使用左加入，并包括VisaExpiryDate IS NULL –

答

使用IS NULL

select EL.Datetimestart, E.Fullname, VisaExpiryDate, E.EmployeeStatusCode 
from EmployeeLog as EL 
inner join Employee as E on E.Employeekey = EL.EmployeeKey 
where DateTimeStart >= Getdate() -1 and (VisaExpiryDate <=GetDate() OR VisaExpiryDate IS NULL)and E.EmployeeStatusCode <> 'x' 
order by VisaExpiryDate

感谢堆，认为它会很简单，但那很容易...现在我觉得自己像一个愚蠢的屁股：P再次感谢

答

，你可以再补充一个条件到where条款：

select EL.Datetimestart, E.Fullname, VisaExpiryDate, E.EmployeeStatusCode 
from EmployeeLog EL inner join 
    Employee E 
    on E.Employeekey = EL.EmployeeKey 
where DateTimeStart >= Getdate() - 1 and 
     (VisaExpiryDate <= GetDate() or VisaExpiryDate is null) and 
     E.EmployeeStatusCode <> 'x' 
order by VisaExpiryDate;

的日期SQL返回null

相关推荐