在 SQL where 子句中使用带有 IsDate 的 case 语句 [英] Using case statements with IsDate in a SQL where clause

问题描述

我正在尝试清理以下代码中的 where 子句语句:

I am trying to clean up the where clause statement in the following code:

SELECT
    CONVERT(datetime, [UTC_Time_Stamp], 127) AS TimeStamp
FROM 
    Table 
WHERE 
    CASE 
       WHEN ISDATE([UTC_Time_Stamp]) = 1 
       THEN CONVERT(datetime, [UTC_Time_Stamp], 127) 
       ELSE CAST('1/1/1900' AS datetime) 
    END > CAST('11/09/2012' AS datetime) 
    AND 
       CASE 
          WHEN ISDATE([UTC_Time_Stamp]) = 1 
          THEN CONVERT(datetime, [UTC_Time_Stamp], 127) 
          ELSE CAST('1/1/3000' AS datetime) 
       END < CAST('11/10/2012' as datetime) 
ORDER BY 
    TimeStamp;

UTC_Time_Stamp 存储为字符串，有时为空.我以前在 where 子句中遇到转换错误.我根据这个问题的建议修复了错误here，但是我觉得必须有一种更简单的方法来实现相同的结果.

UTC_Time_Stamp is stored as a string and is sometimes null. I was previously running into a conversion error inside my where clause. I fixed the error following advice from this question here, but I feel like there has to be a simpler way to achieve the same result.

推荐答案

您需要在 case 语句中进行转换.SQL 是一种描述性语言，不保证处理的顺序.因此，即使在子查询或 CTE 中，where 子句也不一定发生在其他处理之前.但是，SQL 确实保证了 case 语句(不包含带有聚合函数的表达式)中的处理顺序.

You need to do the conversion within a case statement. SQL is a descriptive language and does not guarantee the order of processing. So, a where clause does not necessarily happen before other processing, even when it is in a subquery or a CTE. However, SQL does guarantee the order of processing in a case statement (that contains no expressions with aggregation functions).

您可以使用子查询来简化您的语句:

You can simplify your statement by using a subquery:

select TimeStamp
FROM (select t.*,
             Case When ISDATE([UTC_Time_Stamp]) = 1 Then CONVERT(datetime, UTC_Time_Stamp, 127) end) as TimeStamp
      from Table t
     ) t
WHERE coalesce(TimeStamp, cast('1/1/1900' as datetime)) > cast('11/09/2012' as datetime) and
      coalesce(TimeStamp, cast('1/1/3000' as datetime)) < cast('11/10/2012' as datetime) 
ORDER BY TimeStamp;

这会转换为时间戳，以获取有效值.然后您可以在外部查询中使用该变量.

This does the conversion to TimeStamp, for valid values. You can then use the variable in the outer query.

我还鼓励您习惯日期的 ANSI 标准格式(YYYYMMDD 或 YYYY-MM-DD)，而不是像11/10/2012"这样的模棱两可的格式.您可能很清楚这意味着 2012-11-10 ...或者是 2012-10-11 ...但格式不明确.

I would also encourage you to get used to the ANSI standard format for dates (YYYYMMDD or YYYY-MM-DD) instead of ambiguous formats like '11/10/2012'. It may be clear to you that this means 2012-11-10 . . . or is that 2012-10-11 . . . but the format is ambiguous.

这篇关于在 SQL where 子句中使用带有 IsDate 的 case 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！