平日与Datepart个案

Question

有什么想法吗?下面的代码有什么问题？

我正在尝试写一个case语句，在星期一返回今天的日期，否则，如果今天的日期在星期一，则从表中取最大日期

谢谢

SELECT 
CASE [EffectiveDate]
When datepart(weekday,getdate()) = 1  Then cast(getdate() as date)
Else max ([EffectiveDate])
End,
FROM
[ARCDAL01PR].[ArcTimeSeries].[arc_ts_data].[PriceCurveData]
WHERE
[CurveName] = 'G_H_TTF.EUR'

Answer 1

SELECT 
CASE [EffectiveDate]
When datepart(weekday,getdate()) = 2 Then cast(getdate() as date)
Else max ([EffectiveDate])
End,
FROM
[ARCDAL01PR].[ArcTimeSeries].[arc_ts_data].[PriceCurveData]
WHERE
[CurveName] = 'G_H_TTF.EUR

星期一应该是一周的第二天。最后，如果使用Server 2012，您可以尝试这样做。

SELECT 
CASE [EffectiveDate]
When weekday(getdate(),2) = 1  Then cast(getdate() as date)
Else max ([EffectiveDate])
End,
FROM
[ARCDAL01PR].[ArcTimeSeries].[arc_ts_data].[PriceCurveData]
WHERE
[CurveName] = 'G_H_TTF.EUR

更新

SELECT 
CASE
When datepart(weekday,getdate()) = 2 Then cast(getdate() as date)
Else max ([EffectiveDate])
End as [EffectiveDate]
FROM
[ARCDAL01PR].[ArcTimeSeries].[arc_ts_data].[PriceCurveData]
WHERE
[CurveName] = 'G_H_TTF.EUR'