SQL Server:将字符串转换为小日期时间时出错

Question

谁能帮我整理以下代码摘录。错误似乎在 CASE 语句中，但我无法看到 THEN 之后的所有代码部分都经过单独测试并正常工作。

另请注意，所有日期都采用datetime数据类型yyyy-mm-dd hh:mm:ss.sss

SELECT 
    TICKET_ID,
    CAL_CR.a_DayName,
    CREATED, 
    CAL_CL.a_DayName,
    CLOSED,
    CASE
        WHEN CAL_CR.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CURRENT_TIMESTAMP,CAST(CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME))))
        WHEN CLOSED IS NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CAST(CAST(CAST(CREATED AS DATE) AS VARCHAR) + ' 08:30' AS DATETIME),CREATED)) - (DATEDIFF(MINUTE,CURRENT_TIMESTAMP,CAST(CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME))))
        WHEN CAL_CR.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NOT NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CLOSED,CAST(CAST(CAST(CLOSED AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME))))
        WHEN CAL_CL.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NOT NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CAST(CAST(CAST(CREATED AS DATE) AS VARCHAR) + ' 08:30' AS DATETIME),CREATED)))
        WHEN (CAL_CR.A_DATE IN ('Saturday','Sunday')) AND (CAL_CL.A_DATE IN ('Saturday','Sunday')) AND CLOSED IS NOT NULL THEN (DATEDIFF(WEEKDAY,created, closed)*10*60)
        ELSE (DATEDIFF(WEEKDAY,created, closed)*10*60 - (DATEDIFF(MINUTE,CAST(CAST(CAST(CREATED AS DATE) AS VARCHAR) + ' 08:30' AS DATETIME),CREATED)) - (DATEDIFF(MINUTE,CLOSED,CAST(CAST(CAST(CLOSED AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME))))
    END AS Open_Minutes
FROM 
    OST_TICKET 
LEFT JOIN
    AMI_STATIC.DBO.SB_CALENDAR CAL_CR
    ON
        CAL_CR.a_Date = CAST(CREATED AS DATE)
LEFT JOIN
    AMI_STATIC.DBO.SB_CALENDAR CAL_CL
    ON
        CAL_CL.a_Date = CAST(CLOSED AS DATE)

非常感谢任何帮助。

---

谢谢德姆斯，

采纳了您的建议，并进一步整理了代码。见下文：

SELECT 
    TICKET_ID,
    CAL_CR.a_DayName,
    CREATED, 
    CAL_CL.a_DayName,
    CLOSED,
    CASE
        WHEN CAL_CR.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST(CURRENT_TIMESTAMP AS TIME),CAST('18:00' AS TIME))
        WHEN CLOSED IS NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST('08:30' AS TIME),CAST(CREATED AS TIME)) - DATEDIFF(MINUTE,CAST(CURRENT_TIMESTAMP AS TIME),CAST('18:00' AS TIME))
        WHEN CAL_CR.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NOT NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST(CLOSED AS TIME),CAST('18:00' AS TIME))
        WHEN CAL_CL.A_DATE IN ('Saturday','Sunday') AND CLOSED IS NOT NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST('08:30' AS TIME),CAST(CREATED AS TIME))
        WHEN (CAL_CR.A_DATE IN ('Saturday','Sunday')) AND (CAL_CL.A_DATE IN ('Saturday','Sunday')) AND CLOSED IS NOT NULL THEN DATEDIFF(WEEKDAY,created, closed)*10*60
        ELSE DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST('08:30' AS TIME),CAST(CREATED AS TIME)) - DATEDIFF(MINUTE,CAST(CLOSED AS TIME),CAST('18:00' AS TIME))
    END AS Open_Minutes
FROM 
    OST_TICKET 
LEFT JOIN
    AMI_STATIC.DBO.SB_CALENDAR CAL_CR
    ON
        CAL_CR.a_Date = CAST(CREATED AS DATE)
LEFT JOIN
    AMI_STATIC.DBO.SB_CALENDAR CAL_CL
    ON
        CAL_CL.a_Date = CAST(CLOSED AS DATE)

仍然收到相同的错误：

将字符串转换为小日期时间数据类型时转换失败。

这开始变得有点烦人了。

拆开代码仍然有效

SELECT 
    DATEDIFF(WEEKDAY,created, closed)*10*60 - DATEDIFF(MINUTE,CAST('08:30' AS TIME),CAST(CREATED AS TIME)) - DATEDIFF(MINUTE,CAST(CLOSED AS TIME),CAST('18:00' AS TIME))
FROM 
    OST_TICKET
WHERE 
    TICKET_ID = 30374

谢谢

马库斯

Answer 1

您正在将 DATETIME 转换为 VARCHAR，然后再投射回 DATETIME，例如这里...
- CAST(CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS VARCHAR) + ' 18:00' AS DATETIME)

至少您需要指定一个大小; VARCHAR(16)而不仅仅是VARCHAR. 如果你不这样做，你实际上最多只能得到 1 个字符。

但是，如果你把日期和时间投射到字符串上来操纵它们，你几乎总是在做一些"错误"的事情。 我会推荐一些更像...
- DATEADD(hour, 18, CAST(CURRENT_TIMESTAMP AS DATE))