SQL 检查 CASE 语句中的空日期，我哪里出错了？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2453974/
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
check for null date in CASE statement, where have I gone wrong?
提问by Jimmy
My source table looks like this
我的源表看起来像这样
Id StartDate
1 (null)
2 12/12/2009
3 10/10/2009
I want to create a select statement, that selects the above, but also has an additional column to display a varchar if the date is not null such as :
我想创建一个选择语句，选择上述内容，但如果日期不为空，还有一个额外的列来显示 varchar，例如：
Id StartDate StartDateStatus
1 (null) Awaiting
2 12/12/2009 Approved
3 10/10/2009 Approved
I have the following in my select, but it doesn't seem to be working. All of the statuses are set to Approvedeven though the dates have some nulls
我的选择中有以下内容，但它似乎不起作用。Approved即使日期有一些空值，所有状态都设置为
select
id,
StartDate,
CASE StartDate
WHEN null THEN 'Awaiting'
ELSE 'Approved' END AS StartDateStatus
FROM myTable
The results of my query look like :
我的查询结果如下：
Id StartDate StartDateStatus
1 (null) Approved
2 12/12/2009 Approved
3 10/10/2009 Approved
4 (null) Approved
5 (null) Approved
StartDate is a smalldatetime, is there some exception to how this should be treated?
StartDate 是一个smalldatetime，是否有一些例外应该如何处理？
Thanks
谢谢
回答by Paddy
Try:
尝试：
select
id,
StartDate,
CASE WHEN StartDate IS NULL
THEN 'Awaiting'
ELSE 'Approved' END AS StartDateStatus
FROM myTable
You code would have been doing a When StartDate = NULL, I think.
你的代码会一直在做一个 When StartDate = NULL，我想。
NULLis never equal to NULL(as NULL is the absence of a value). NULLis also never not equal to NULL. The syntax noted above is ANSI SQL standard and the converse would be StartDate IS NOT NULL.
NULL永远不等于NULL（因为 NULL 是没有值）。 NULL也永远不等于NULL。上面提到的语法是 ANSI SQL 标准，反之则是StartDate IS NOT NULL.
You can run the following:
您可以运行以下命令：
SELECT CASE WHEN (NULL = NULL) THEN 1 ELSE 0 END AS EqualityCheck,
CASE WHEN (NULL <> NULL) THEN 1 ELSE 0 END AS InEqualityCheck,
CASE WHEN (NULL IS NULL) THEN 1 ELSE 0 END AS NullComparison
And this returns:
这将返回：
EqualityCheck = 0
InEqualityCheck = 0
NullComparison = 1
For completeness, in SQL Server you can:
为完整起见，在 SQL Server 中，您可以：
SET ANSI_NULLS OFF;
Which would result in your equals comparisons working differently:
这将导致您的 equals 比较以不同的方式工作：
SET ANSI_NULLS OFF
SELECT CASE WHEN (NULL = NULL) THEN 1 ELSE 0 END AS EqualityCheck,
CASE WHEN (NULL <> NULL) THEN 1 ELSE 0 END AS InEqualityCheck,
CASE WHEN (NULL IS NULL) THEN 1 ELSE 0 END AS NullComparison
Which returns:
返回：
EqualityCheck = 1
InEqualityCheck = 0
NullComparison = 1
But I would highly recommend against doing this. People subsequently maintaining your code might be compelled to hunt you down and hurt you...
但我强烈建议不要这样做。随后维护你的代码的人可能会被迫追捕你并伤害你......
Also, it will no longer work in upcoming versions of SQL server:
此外，它将不再适用于即将推出的 SQL Server 版本：
回答by Bubba
select Id, StartDate,
Case IsNull (StartDate , '01/01/1800')
When '01/01/1800' then
'Awaiting'
Else
'Approved'
END AS StartDateStatus
From MyTable
