在SQL的where子句中使用IsDate和case语句

Question

我尝试着优化以下代码中的where子句语句：

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的建议，我已经修复了这个错误，但我感觉还有更简单的方法来实现同样的结果。

- Corsae

---

这是9月11日还是11月9日？ - Tony Hopkinson

11月9日（mm/dd/yyyy）。 - Corsae

Answer 1

你需要在case语句内进行转换。 SQL是一种描述性语言，不能保证处理顺序。所以，where子句不一定先于其他处理执行，即使它位于子查询或CTE中也是如此。但是，SQL确保case语句（不包含聚合函数表达式）的处理顺序。

你可以使用子查询简化语句：

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;

对于有效值，这将进行时间戳转换。然后您可以在外部查询中使用该变量。

我还鼓励您习惯使用日期的ANSI标准格式（YYYYMMDD或YYYY-MM-DD），而不是像“11/10/2012”这样模糊的格式。这可能对您来说很清楚，表示的是2012年11月10日，还是2012年10月11日……但格式是模糊的。

- Gordon Linoff

Answer 2

我喜欢使用CTE，或者你也可以使用临时表、表变量或者像@Derek一样的内联派生表。

基本上，我们首先要获取正确的数据类型，然后创建查询会更加容易：

;with CTE as (
    -- Bring back the column as datetime
    select case when isdate(UTC_Time_Stamp) = 1 then cast(UTC_Time_Stamp as datetime) end as UTC_Time_Stamp
    from [Table]
)
-- Simple select with the proper datatype
select convert(varchar(50), UTC_Time_Stamp, 127) as [TimeStamp]
from CTE
-- May still need gt and lt functionality
where UTC_Time_Stamp between cast('11/09/2012' as datetime) and cast('11/10/2012' as datetime)

看起来你在使用一些任意的小数和大数值来表示 TimeStamp，这可能是不必要的，因为你只是在比较它们而已，所以我把它们移除了。

请注意，我在 CTE 中使用了 datetime 数据类型，并且仅在演示时将其转换为字符串进行比较。

另外请注意，between 是包含的，所以你可能需要回到单独使用 > 和 < 的 where 子句。

- Tim Lehner

Answer 3

像这样做起来更容易（使用谓词中的任何转换/之间子句）：

SELECT CONVERT(datetime, [UTC_Time_Stamp], 127) as TimeStamp
FROM (
  select [UTC_Time_Stamp] 
  from Table 
  WHERE ISDATE([UTC_Time_Stamp]) = 1 
) a
WHERE
  convert(datetime, [UTC_Time_Stamp], 127) between '11/9/2012' and '11/10/2012'
ORDER BY TimeStamp;

- Derek Kromm

Answer 4

在我的经验中，这解决了使用临时表别名或子查询可能会减慢选择数百万条记录的问题。

select your_column 
from your_table 
where case when ISDATE(your_column) = 1 
           then Cast(your_column as date) 
      end Between '01/01/2016' and '01/01/2017' 
order by your_column

敬礼

- jose lopez