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)

尽管如此，仍然遇到相同的错误：

将字符串转换为smalldatetime数据类型时转换失败。

这开始变得有点烦人。

拆开代码仍然有效

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

谢谢，

Marcus

Answer 1

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

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

但是，如果您将日期和时间转换为字符串来操纵它们，那么您几乎总是在做一些“错误”的事情。我会推荐一些更像......
- DATEADD(小时, 18, CAST(CURRENT_TIMESTAMP AS DATE))