将varchar转换为日期

Question

我认为我已经阅读了关于这个主题的所有帖子，但是没有一个能解决我的问题。

首先，我正在使用的数据库将所有日期字段设置为varchar数据类型，这使我非常烦恼，因为每当我使用涉及日期的查询时都必须进行强制转换或转换。

我目前正在处理的查询应该提取一份医学患者列表，他们都有特定的诊断，但是该诊断必须在某个日期之前给出。这里是我的代码。

SELECT DISTINCT
    pd.PatientID,
    pd.PatientName,
    pmh.DateOfOnset
    pmh.DiagnosisCode
FROM PatientDemographic pd JOIN PatientMedicalHistory pmh ON pd.PatientID = pmh.PatientID
WHERE pmh.DiagnosisCode = '401.1'
    AND CAST(pmh.DateOfOnset as Date) > CAST('12/31/2014' as Date)

我遇到了一个错误，错误信息是“无法将日期和/或时间从字符字符串转换”。它告诉我错误在第一行（SELECT DISTINCT 行），这并没有提供太多帮助，我不确定需要修复什么。

正如我之前提到的，DateOfOnset 字段是 varchar 数据类型，我需要找到任何一个在2014年12月底之前的日期。我尝试通过尝试不同的 CAST 语句组合来解决此错误 - 我甚至尝试在 SELECT 语句中的日期字段上包含一个 cast，但仍然出现相同的错误。

我之前正在处理另一个查询，该查询要求我查找一定时间范围内的所有患者预约，并且对于该查询，我的 WHERE 子句设置如下：

WHERE Cast(VisitDate as Date) BETWEEN CAST('01/01/2014' as Date) AND CAST('12/01/2014' as Date)

...而且它完美地运作了。由于我当前的查询设置方式与之前几乎相同，所以我不确定为什么会出现转换错误。

- EJF

---

可能在pmh.DateOfOnset中有无效日期。 - FLICKER

错误发生在第1行，因为第1行是包含错误的语句的开始。当运行选择语句时会出现该错误。 - GendoIkari

可能是文化差异。12/31 可能被解释为 31/12。 - Johnno Nolan

我只是困惑为什么我的其他查询使用VisitDate可以正常工作，而这个却不行。我一直在使用月-日-年的日期格式，并且以前从未遇到过任何问题。 - EJF

Answer 1

您的日期格式有误：

SET DATEFORMAT dmy;
SELECT CAST('12/31/2014' as Date);
--Conversion failed when converting date and/or time from character string.

在执行查询之前，您可以将其设置为mdy。

SET DATEFORMAT mdy;
SELECT CAST('12/31/2014' as Date);

实时演示

或者使用 转换 并且样式为 101：

SET DATEFORMAT dmy;
SELECT CONVERT(DATE,'12/31/2014',101)

---

如果您确实需要将DATE作为VARCHAR存储，至少使用与文化无关的类型，如ISO-8601。

SET DATEFORMAT dmy;
SELECT CAST('20140201' as Date);
-- 01.02.2014

SET DATEFORMAT mdy;
SELECT CAST('20140201' as Date)
-- 01.02.2014

- Lukasz Szozda

Answer 2

听起来好像SQL无法将存储的字符串转换为日期。这就解释了为什么CAST(pmh.DateOfOnset as Date)失败而Cast(VisitDate as Date)不会失败——后者可能没有任何格式错误的日期。

最好的解决方案是将表列转换为正确的数据类型。其次，添加包含正确数据类型的列，并进行数据转换；您需要修复任何现有的坏数据，并在加载数据时动态转换数据（很烦人）。另一个选择是添加计算列，但是您将遇到前面提到的无效日期问题。（您使用的是哪个版本的SQL？较新版本具有isdate函数，这可能有所帮助。）

如果修改表不是一个选项，那么您可能被迫编写查询，假设一些数据无效（格式错误）。

- Philip Kelley

---

是的，我可能被困在后者的选择中了。数据库连接到医疗记录系统，所有日期都被输入为字符串，因为它们来自系统中的自由文本字段（不知道哪个天才设置了这样一种方式，但这为许多不同的表中存在大量错误数据留下了空间）。我可能会搜索所有诊断结果，并将结果粘贴到Excel中，然后按2014年及之前的时间进行过滤。这不是非常高效，但浪费时间来解决我无法修复的错误也不是。 - EJF

更新：是的，在再次查看后，似乎有相当多的日期只是空白的。这可能是让我困扰的原因。 - EJF

如果日期为空，您可以忽略它们或使用默认日期值吗？（例如今天、1980年1月1日或类似日期） - Philip Kelley

我将尝试排除所有的空格并观察会发生什么！ - EJF