仅当 varchar 包含有效日期时，如何将其转换为日期？

我正在寻找有关 SQL Server Management Studio 的我认为非常有用的信息。

我有一个带有 type 列的表varchar存储日期、数字和字符串。

这些日期以以下格式存储：

dd/mm/aaaa

我有一个搜索匹配行的查询，一个要求是用户必须能够在日期（时间段）之间进行搜索。

如果我只有日期，那就没有什么神秘的，我可以使用查询：

where convert(datetime,a.valor,103) between '01/01/2013' and '03/01/2013'

问题是，当到达值不是日期的行时，此查询会失败。

由于可能有数千行需要搜索，执行该查询的有效方法是什么？

典型的答案是添加一个 WHERE 子句：

WHERE ISDATE(a.valor) = 1

然而，由于以下几个原因，这在您的情况下是有问题的：

1. ISDATE()不一定符合您想要的方式，具体取决于服务器的区域设置、用户的语言或日期格式选项等。例如：

   SET DATEFORMAT dmy;
   SELECT ISDATE('13/01/2012'); -- 1
   
   SET DATEFORMAT mdy;
   SELECT ISDATE('13/01/2012'); -- 0
   

2. 您无法真正控制 SQL Server 将尝试执行CONVERT过滤器后。

您甚至不能使用子查询或 CTE 来尝试将过滤器与 CONVERT 分开，因为 SQL Servercan以它认为更有效的任何顺序优化查询中的操作。

例如，对于有限的样本，您可能会发现这工作正常：

SET DATEFORMAT dmy;

SELECT valor, valor_date FROM (
  SELECT valor, valor_date = CONVERT(DATE, 
    CASE WHEN ISDATE(valor) = 1 THEN valor ELSE NULL END, 103)
  FROM dbo.mytable
  WHERE ISDATE(valor) = 1
) AS sub WHERE valor_date BETWEEN '01/01/2012' AND '01/03/2012';

但我也见过这种构造的情况，其中 SQL Server 尝试首先评估过滤器，导致您当前遇到的相同错误。

一些更安全的解决方法：

添加计算列，例如

ALTER TABLE dbo.mytable ADD valor_date
  AS CONVERT(DATE, CASE WHEN ISDATE(valor) = 1 THEN valor 
    ELSE NULL END, 103);

为了保护自己在运行时免受可能的误解，您应该在发出引用计算列的查询之前指定日期格式，例如

SET DATEFORMAT dmy;
SELECT valor, valor_date FROM dbo.mytable WHERE ...;

创建视图：

CREATE VIEW dbo.myview
AS
  SELECT valor, valor_date = CONVERT(DATE, 
    CASE WHEN ISDATE(valor) = 1 THEN valor ELSE NULL END, 103)
  FROM dbo.mytable
  WHERE ISDATE(valor) = 1;

同样，您需要发出SET DATEFORMAT查询视图时。

使用临时表：

SELECT <cols>
INTO #foo
FROM dbo.mytable
WHERE ISDATE(valor) = 1;

SELECT <cols>, CONVERT(DATE, valor) FROM #foo WHERE ...;

您可能仍想使用DATEFORMAT保护自己免受之间冲突的影响ISDATE和用户设置。

不，你应该not尝试使用字符串模式匹配将字符串验证为日期，如另一个（现已删除）答案中的建议：

like '%__/%' or like '%/%'

您必须在那里进行一些相当复杂且严格的验证才能处理所有有效日期，包括闰年。