SQL Server语言 - 在 case 语句中组合日期字段、文字字符和 DATEADD 值

Question

我创建了一个案例语句来按此顺序工作。

确定是"优先级升级"、"准备取件"、"等待路由"，还是日期范围。

如果我排除案例陈述的ELSE部分，上述内容有效。 如果其他情况都不正确，那么我需要我的 else 语句来生成发货日期加上添加到发货日期的 2 天，以给出包裹发货的日期范围。 即 02/15/2016 – 02/17/2016

Select
    Case 
        When datediff (day, sl.[Shipment Date], sl.[Priority Escalation Date]) < 0 then  'Priority Escalation'
        When sh.[On Hold] = 'RFP' then 'Ready for Pickup'
        When sh.[On Hold] = 'WFR' then 'Waiting for Routing'
        Else sl.[Shipment date] + “ – “ + & DateAdd("d", 2, sl.[Shipment Date]) 
    End as 'Expected Value'

这是数据应该的样子

Expected Value
02/24/2016 - 02/26/2016
02/24/2016 - 02/26/2016
02/24/2016 - 02/26/2016
02/24/2016 - 02/26/2016
Waiting for Routing
Waiting for Routing
Priority Escalation
Ready for Pickup
Ready for Pickup
Priority Escalation
Priority Escalation
Priority Escalation
Ready for Pickup

Answer 1

您可能遇到了转换错误。要解决此问题，您需要将日期CONVERT为VARCHAR：

SELECT
    CASE 
        WHEN DATEDIFF (DAY, sl.[Shipment Date], sl.[Priority Escalation Date]) < 0 THEN  'Priority Escalation'
        WHEN sh.[On Hold] = 'RFP' THEN 'Ready for Pickup'
        WHEN sh.[On Hold] = 'WFR' THEN 'Waiting for Routing'
        ELSE CONVERT(VARCHAR(10), sl.[Shipment date], 101) + ' - ' 
                + CONVERT(VARCHAR(10), DATEADD(DAY, 2, sl.[Shipment Date]), 101) 
    END AS 'Expected Value'