如何在下周三17时在Server 2008中查找目标数据

Question

我们有一个包裹递送系统，它将收到的包裹记录为ParcelReceivedDate，一个日期时间字段。

我们必须定义一个目标交货日期和时间的方式，任何包裹收到的星期六或周日必须有一个目标交货日期为下周三格林尼治时间1700格林尼治时间。

 CASE 
     WHEN Priority = 'Normal' and DATENAME(weekday,ParcelReceivedDate) IN ('Saturday') 
     THEN ??

     WHEN Priority = 'Normal' and DATENAME(weekday,ParcelReceivedDate) IN ('Sunday')  
     THEN ??

     Else DATEADD(hour,24,ParcelReceivedDate)
 END as ParcelTargetDate

Answer 1

试试这个：

CASE 
    WHEN Priority = 'Normal' and DATENAME(weekday,ParcelReceivedDate) IN ('Saturday') 
    THEN
        dateadd(second, -datepart(second, ParcelReceivedDate), 
            dateadd(minute, -datepart(minute, ParcelReceivedDate), 
                dateadd(hour, 17 - datepart(hour, ParcelReceivedDate), 
                    dateadd(day, 4, ParcelReceivedDate)
                )
            )
        )
    WHEN Priority = 'Normal' and DATENAME(weekday,ParcelReceivedDate) IN ('Sunday')  
    THEN
        dateadd(second, -datepart(second, ParcelReceivedDate), 
            dateadd(minute, -datepart(minute, ParcelReceivedDate), 
                dateadd(hour, 17 - datepart(hour, ParcelReceivedDate), 
                    dateadd(day, 3, ParcelReceivedDate)
                )
            )
        )
    ELSE DATEADD(hour,24,ParcelReceivedDate
END as ParcelTargetDate

你知道你是在星期六还是星期天，所以你加上抵消日到星期三。