SQL 检查空日期然后大小写切换

Question

我有一个数据结果，我想按两个不同的日期进行过滤。

除非completion_date 为空，否则以下内容正常工作。如果它为空，我想使用另一个名为created_date的字段。

string strSQL = string.Format(@"select * from my_table 

            where user_Id = {0}

            AND [completion_date] between {1} AND {2}

            order by completion_status  desc, [completion_date] asc"
            , userId, dateStart, dateEnd, visibilityIndicator);

也许是这样的：

string strSQL = string.Format(@"select * from my_table                                                   
where user_Id = {0}


CASE completion_date 
     WHEN is null THEN [completion_date] between {1} AND {2}
     ELSE [created_date] between {1} AND {2}

END 

AND                       
            order by completion_status  desc, [completion_date] asc"
            , userId, dateStart, dateEnd, visibilityIndicator);

检查该空值然后切换到created_date的最佳方法是什么？

Answer 1

where isnull(completetion_date,created_date) between {1} and {2}

应该有效

Answer 2

string strSQL = string.Format(@"select * from my_table 
where user_Id = {0}
AND ISNULL([completion_date],[created_date]) between {1} AND {2}
 order by completion_status  desc, [completion_date] asc"

Answer 3

符合 ANSI SQL 的版本：

SELECT * FROM my_table WHERE user_Id = {0}
AND [completion_date] BETWEEN {1} AND {2}
OR ([completion_date] IS NULL AND [created_date] BETWEEN {1} AND {2})
ORDER BY completion_status DESC, [completion_date] ASC