我们有一个包裹递送系统,该系统将接收到的包裹记录为日期时间字段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
试试这个:
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
您知道您是在周六还是周日,因此您可以添加偏移天数以达到周三
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句