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

Question

我可能已经看了其他所有问题，尝试了其他所有答案，但都没有成功，所以我要放弃我想做的事情，希望我能得到一些帮助。

以下是我目前正在做的工作。

SELECT t1.SOURCE_ID, t1.[Agency Name], t1.[Title Name], t1.DESCR, t1.CREATED_DATE AS 'Reviewed Date', t2.CREATED_DATE AS 'Filed Dated', DateDiff(Day,'Filed Date','Reviewed Date') AS 'Difference' 
FROM ((SELECT h.SOURCE_ID, h.CHANGE_VALUE_FROM, h.CHANGE_VALUE_TO, h.CREATED_DATE, rp.EFFECTIVE_DATE, 
tv.DESCR, a.NAME as 'Agency Name', p.NAME as 'Title Name' FROM HISTORY AS h 
INNER JOIN RULE_PACKET AS rp ON (h.SOURCE_ID = rp.RULE_PACKET_ID)
INNER JOIN PROGRAM AS p ON (p.PROGRAM_ID = rp.PROGRAM_ID)
INNER JOIN AGENCY AS a ON (a.AGENCY_ID = p.AGENCY_ID)
LEFT OUTER JOIN TYPE_V AS tv ON (tv.TYPE_ID = rp.ACTION_TYPE_ID)
WHERE (h.CHANGE_VALUE_TO = 'Rules Pre-Approved' OR h.CHANGE_VALUE_TO = 'CPR Rules Pre-Approved') AND (h.CREATED_DATE  >= '2020-02-01 00:00:00.000')) AS t1 
INNER JOIN (SELECT h.SOURCE_ID, h.CHANGE_VALUE_FROM, h.CHANGE_VALUE_TO, h.CREATED_DATE, rp.EFFECTIVE_DATE, tv.DESCR, a.NAME as 'Agency Name', p.NAME as 'Title Name' FROM HISTORY as h 
INNER JOIN RULE_PACKET AS rp ON (h.SOURCE_ID = rp.RULE_PACKET_ID)
INNER JOIN PROGRAM AS p ON (p.PROGRAM_ID = rp.PROGRAM_ID)
INNER JOIN AGENCY AS a ON (a.AGENCY_ID = p.AGENCY_ID)
LEFT OUTER JOIN TYPE_V AS tv ON (tv.TYPE_ID = rp.ACTION_TYPE_ID)
WHERE (h.CHANGE_VALUE_TO = 'Standard Packet Filed') AND (h.CREATED_DATE  >= '2020-02-01 00:00:00.000')) AS t2 ON (t1.SOURCE_ID = t2.SOURCE_ID) AND ('Reviewed Date' >= 'Filed Date'));

我得到的错误是：

消息241，级别16，状态1，行1从字符串转换日期和/或时间时转换失败。

我正在努力了解从提交到审查需要多长时间。

当我取出DateDiff函数时，这就是我的代码。

SELECT t1.SOURCE_ID, t1.[Agency Name], t1.[Title Name], t1.DESCR, t1.CREATED_DATE AS 'Reviewed Date', t2.CREATED_DATE AS 'Filed Dated' 
FROM ((SELECT h.SOURCE_ID, h.CHANGE_VALUE_FROM, h.CHANGE_VALUE_TO, h.CREATED_DATE, rp.EFFECTIVE_DATE, 
tv.DESCR, a.NAME as 'Agency Name', p.NAME as 'Title Name' FROM HISTORY AS h 
INNER JOIN RULE_PACKET AS rp ON (h.SOURCE_ID = rp.RULE_PACKET_ID)
INNER JOIN PROGRAM AS p ON (p.PROGRAM_ID = rp.PROGRAM_ID)
INNER JOIN AGENCY AS a ON (a.AGENCY_ID = p.AGENCY_ID)
LEFT OUTER JOIN TYPE_V AS tv ON (tv.TYPE_ID = rp.ACTION_TYPE_ID)
WHERE (h.CHANGE_VALUE_TO = 'Rules Pre-Approved' OR h.CHANGE_VALUE_TO = 'CPR Rules Pre-Approved') AND (h.CREATED_DATE  >= '2020-02-01 00:00:00.000')) AS t1 
INNER JOIN (SELECT h.SOURCE_ID, h.CHANGE_VALUE_FROM, h.CHANGE_VALUE_TO, h.CREATED_DATE, rp.EFFECTIVE_DATE, tv.DESCR, a.NAME as 'Agency Name', p.NAME as 'Title Name' FROM HISTORY as h 
INNER JOIN RULE_PACKET AS rp ON (h.SOURCE_ID = rp.RULE_PACKET_ID)
INNER JOIN PROGRAM AS p ON (p.PROGRAM_ID = rp.PROGRAM_ID)
INNER JOIN AGENCY AS a ON (a.AGENCY_ID = p.AGENCY_ID)
LEFT OUTER JOIN TYPE_V AS tv ON (tv.TYPE_ID = rp.ACTION_TYPE_ID)
WHERE (h.CHANGE_VALUE_TO = 'Standard Packet Filed') AND (h.CREATED_DATE  >= '2020-02-01 00:00:00.000')) AS t2 ON (t1.SOURCE_ID = t2.SOURCE_ID) AND ('Reviewed Date' >= 'Filed Date'));

这就是它所产生的：

SSMS 结果的屏幕截图

请帮忙。

Answer 1

您需要在datediff((函数和where子句中使用t1.CREATED_Date和t2.CREATED_Date，而不是"Reviewed Date"one_answers"Filed Dated"，因为在where子句或选择列表中不允许使用列别名。

当您在datediff((函数中使用"Reviewed Date"或"Filed Dated"时，它试图将字符串"Reviewd Date"one_answers"Filed Date"转换为日期字段，这是不可能的。

请尝试这个：

SELECT t1.SOURCE_ID, t1.[Agency Name], t1.[Title Name], t1.DESCR, t1.CREATED_DATE AS 'Reviewed Date', t2.CREATED_DATE AS 'Filed Dated', DateDiff(Day,t2.CREATED_DATE,t1.CREATED_DATE) AS 'Difference' 
FROM ((SELECT h.SOURCE_ID, h.CHANGE_VALUE_FROM, h.CHANGE_VALUE_TO, h.CREATED_DATE, rp.EFFECTIVE_DATE, 
tv.DESCR, a.NAME as 'Agency Name', p.NAME as 'Title Name' FROM HISTORY AS h 
INNER JOIN RULE_PACKET AS rp ON (h.SOURCE_ID = rp.RULE_PACKET_ID)
INNER JOIN PROGRAM AS p ON (p.PROGRAM_ID = rp.PROGRAM_ID)
INNER JOIN AGENCY AS a ON (a.AGENCY_ID = p.AGENCY_ID)
LEFT OUTER JOIN TYPE_V AS tv ON (tv.TYPE_ID = rp.ACTION_TYPE_ID)
WHERE (h.CHANGE_VALUE_TO = 'Rules Pre-Approved' OR h.CHANGE_VALUE_TO = 'CPR Rules Pre-Approved') AND (h.CREATED_DATE  >= '2020-02-01 00:00:00.000')) AS t1 
INNER JOIN (SELECT h.SOURCE_ID, h.CHANGE_VALUE_FROM, h.CHANGE_VALUE_TO, h.CREATED_DATE, rp.EFFECTIVE_DATE, tv.DESCR, a.NAME as 'Agency Name', p.NAME as 'Title Name' FROM HISTORY as h 
INNER JOIN RULE_PACKET AS rp ON (h.SOURCE_ID = rp.RULE_PACKET_ID)
INNER JOIN PROGRAM AS p ON (p.PROGRAM_ID = rp.PROGRAM_ID)
INNER JOIN AGENCY AS a ON (a.AGENCY_ID = p.AGENCY_ID)
LEFT OUTER JOIN TYPE_V AS tv ON (tv.TYPE_ID = rp.ACTION_TYPE_ID)
WHERE (h.CHANGE_VALUE_TO = 'Standard Packet Filed') AND (h.CREATED_DATE  >= '2020-02-01 00:00:00.000')) AS t2 ON (t1.SOURCE_ID = t2.SOURCE_ID) AND (t1.CREATED_DATE >= t2.CREATED_DATE));