SQL Server: ошибка преобразования строки в smalldatetime?

Question

Может кто-нибудь помочь мне со следующим фрагментом кода, который я собрал. Ошибка, кажется, в CASE Заявление, но я не могу видеть, где, поскольку все части кода после ТО были проверены индивидуально и работают.

Также следует отметить, что все даты в 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

Спасибо,

Маркус

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