對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2017/3/21 7:02:09
check for null date in CASE statement, where have I gone wrong?
My source table looks like this
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 :
I have the following in my select, but it doesn't seem to be working. All of the statuses are set to Approved even though the dates have some nulls
The results of my query look like :
StartDate is a smalldatetime, is there some exception to how this should be treated?
Thanks
Try:
You code would have been doing a When StartDate = NULL, I think.
NULL is never equal to NULL (as NULL is the absence of a value). NULL is also never not equal to NULL. The syntax noted above is ANSI SQL standard and the converse would be StartDate IS NOT NULL.
You can run the following:
And this returns:
For completeness, in SQL Server you can:
Which would result in your equals comparisons working differently:
Which returns:
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:
https://msdn.microsoft.com/en-GB/library/ms188048.aspx
原文出處：sql - check for null date in CASE statement, where have I gone wrong? - Stack Overflow
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 :
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 Approved even though the dates have some nulls
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?
Thanks
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.
NULL is never equal to NULL (as NULL is the absence of a value). NULL is also never not equal to NULL. The syntax noted above is ANSI SQL standard and the converse would be 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:
SET ANSI_NULLS OFF;
Which would result in your equals comparisons working differently:
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:
https://msdn.microsoft.com/en-GB/library/ms188048.aspx
select Id, StartDate,
Case IsNull (StartDate , '01/01/1800')
When '01/01/1800' then
'Awaiting'
Else
'Approved'
END AS StartDateStatus
From MyTable
原文出處：sql - check for null date in CASE statement, where have I gone wrong? - Stack Overflow