CAST和CONVERT在尝试将字符串转换为日期时均失败

Question

我正在处理一个表，该表包含在完成一项活动后对人们进行的问卷调查的记录。问卷上有几个问题，所以每个人都有多个相同收集日期的记录，就像这样

PersonID   Question            Result         CollectedDate
-------------------------------------------------------------
1001       First activity?     Yes            10/23/2022
1001       Activity date       10/20/2022     10/23/2022
1001       Activity type       Painting       10/23/2022
1002       First activity?     No             10/24/2022
1002       Activity date       10/23/2022     10/24/2022
1002       Activity type       Writing        10/24/2022

由于我的最终目标是将活动日期与问卷收集日期进行比较，并查看两者之间经过了多长时间，所以我稍微改变了一下我的查询，所以我只关注每个人关于活动日期的问题。这是一个超级简单的查询：

SELECT
PersonID,
Question,
Result,
CollectedDate
FROM Questionnaire
WHERE Question LIKE '%date%'

PersonID   Question            Result         CollectedDate
-------------------------------------------------------------
1001       Activity date       10/20/2022     10/23/2022
1002       Activity date       10/23/2022     10/24/2022

我的主要问题是，为了容纳文本答案，Result字段是varchar(50)，所以在那里看到的任何日期实际上都来自前端界面中的自由文本字段。我已经尝试使用CAST()和CONVERT()将其转换为实际的日期格式，以便可以计算日期之间的差异。我看到了以下两个错误，这取决于我正在使用的功能或我试图应用的日期/时间样式：

Conversion failed when converting date and/or time from character string

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

我试过：

SELECT
PersonID,
Question,
CAST(Result as date),
CollectedDate
FROM Questionnaire
WHERE Question LIKE '%date%'

而且。。。

SELECT
PersonID,
Question,
CONVERT(DATETIME,Result,101) as Result,
CollectedDate
FROM Questionnaire
WHERE Question LIKE '%date%'

并尝试了几种不同的风格。有人有什么进一步的建议吗？日期本身可能是问题所在，还是Result字段也包含一堆其他内容，尽管它目前在查询结果中被省略了？

更新：即使我过滤掉了其他问题类型，这个Result字段中也有一些不稳定的日期格式(我讨厌自由文本(。例如，有一些格式类似05/01/2022，而另一些格式类似于5/1/2022。有些人有类似5/19/2022 - 5/20/2022的东西，比如这个人可能记不清他们活动的确切日期。处理这一切的最佳方法是什么？

Answer 1

您应该能够通过确保拒绝任何无法转换为日期的值来克服错误。在很大程度上，这就是：

Result = CASE 
WHEN ISDATE(Result) = 1 THEN CONVERT(date, Result, 101) END

你可能认为说WHERE Question = 'Activity Date' AND ISDATE(Result) = 1就足够了，但是：

1. 仍有人可能在该行输入了错误数据
2. SQL Server可能会尝试在筛选器之前执行CONVERT()操作

您可以使用来识别那些有坏数据的

WHERE Question = 'Activity Date' AND ISDATE(Result) = 0

但是，在您修复了结构并将日期存储在一个独立的列中之前，修复这些数据只意味着它再次发生只是时间问题。

同时，您可能会考虑将用户输入的内容显示为字符串，而不是试图将其转换为日期。尤其是因为101可能是一个糟糕的猜测——如果用户来自英国或加拿大怎么办？他们可能已经进入05/12，意味着12月5日，而不是5月12日。