时间戳

首页 > 解决方案 > 使用 DateDiff 从字符串转换日期和/或时间时转换失败

sql - 使用 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', 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 结果的屏幕截图

请帮忙。

标签： sqlsql-serverdatetsqldatediff

解决方案

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

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

请试试这个：

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));

推荐阅读