使用 CASE 比较时出现日期时间'Conversion failed'错误

Question

我有一个表，用于存储字段/值对进行比较，例如：

ID    FieldName    ValueA      ValueB
----  ----------   -------     ------
1     Name         ABC         XYZ
1     StartDate    2015-05-04  2015-06-05
1     Amount       4           4
2     Name         DEF         UVW
2     StartDate    2015-03-22  2015-02-11
2     Amount       4           4

我正在尝试比较ValueA和ValueB的值，寻找不匹配的值。由于它们以varchar（max）的形式存储在表中，有时会出现不匹配的地方，即

ID    FieldName    ValueA      ValueB
----  ----------   -------     ------
3     StartDate    2015-01-30  01/30/2015

因此，在比较之前，我使用CASE语句来转换数据类型，即

Select *
From ComparisonTable
where
(case when FieldName = 'StartDate' then CAST(ValueA as datetime)
      when FieldName = 'Amount' then CAST(ValueA as int)
      else CAST(ValueA as varchar(max))
 end)
=
(case when FieldName = 'StartDate' then CAST(ValueB as datetime)
      when FieldName = 'Amount' then CAST(ValueB as int)
      else CAST(ValueB as varchar(max))
 end)

我收到一个错误

从字符转换日期和/或时间时转换失败字符串

我想这是因为在某个地方，StartDate值输入错误。然而，我进行了以下搜索：

Select *
From ComparisonTable
where FieldName = 'StartDate'
and
(ISDATE(ValueA) = 0
OR
ISDATE(ValueB) = 0)

对我来说，没有任何东西表明所有的日期值都存储正确。

我想知道是否有其他人也遇到过类似的问题，或者SQL/SQL Server中是否存在不允许在WHERE/CASE语句中进行这些类型的数据类型转换的固有问题？

更新我尝试在一个简单的SELECT:中使用相同的CASE

SELECT
    (case when FieldName = 'StartDate' then CAST(ValueA as datetime)
          when FieldName = 'Amount' then CAST(ValueA as int)
          else CAST(ValueA as varchar(max))
     end)
    =
    (case when FieldName = 'StartDate' then CAST(ValueB as datetime)
          when FieldName = 'Amount' then CAST(ValueB as int)
          else CAST(ValueB as varchar(max))
     end)
WHERE FieldName IN ('StartDate','Amount')

而EVERYTHING返回为datetime，这意味着即使是int值也被转换为日期，这让我相信这种类型的CASE强制转换是不允许的。尽管CASE表示要将"Name"字段转换为varchar，但它却将所有内容转换为datetime。

如果这是标准的SQL Server行为，那么有什么变通方法可以用来进行这些转换吗？

Answer 1

我看到您正在尝试做什么，但不能对这样的单个值使用多个类型。它们都需要是相同的数据类型才能按照您尝试的方式进行操作。

这应该是您想要的，但这将导致较差的性能，因为没有索引可以用于这种类型的比较。

Select  *
From    ComparisonTable
Where   
(
    FieldName = 'StartDate'
And Cast(ValueA As DateTime) = Cast(ValueB As DateTime)
)
Or
(
    FieldName = 'Amount'
And Cast(ValueA As Int) = Cast(ValueB As Int)
)
Or
(
    FieldName Not In ('StartDate', 'Amount')
And Cast(ValueA As Varchar (Max)) = Cast(ValueB As Varchar (Max))
)

另一种选择是在数据类型转换后将所有内容转换为VARCHAR：

Select  *
From    ComparisonTable
Where   
(
    case    
        when FieldName = 'StartDate' then CAST(CAST(ValueA as datetime) As Varchar (Max))
        when FieldName = 'Amount' then CAST(CAST(ValueA as int) AS Varchar (Max))
        else CAST(ValueA as varchar(max))
    end
)
=
(
    case 
        when FieldName = 'StartDate' then CAST(CAST(ValueB as datetime) AS Varchar(Max))
        when FieldName = 'Amount' then CAST(CAST(ValueB as int) As Varchar (Max))
        else CAST(ValueB as varchar(max))
    end
)

Answer 2

我提出的一个解决方案是使用UNION ALL来分离不同的类型：

SELECT *
FROM ComparisonTable
WHERE FieldName = 'StartDate'
AND CAST(ValueA as datetime) = CAST(ValueB as datetime)
UNION ALL
SELECT *
FROM ComparisonTable
WHERE FieldName = 'Amount'
AND CAST(ValueA as int) = CAST(ValueB as int)
UNION ALL
SELECT *
FROM ComparisonTable
WHERE FieldName = 'Name'
AND CAST(ValueA as varchar(max)) = CAST(ValueB as varchar(max))

它是有效的，但为了提高效率，我会将其与其他方法进行比较。