允许在WHERE CASE WHEN子句中对要检查的列进行不同转换的多个CASE

使用 Like 而不是 = :

WHERE CONVERT(varchar(10),D.[Date],120) LIKE 
CASE @SortOrder 
    WHEN '1' THEN CONVERT(varchar(10),GETDATE(),120)
    WHEN '2' THEN CONVERT(varchar(7),GETDATE(),120) +'%'
    WHEN '3' THEN CONVERT(varchar(4),GETDATE(),120) +'%'
END

另一个选项(不使用case)是:

WHERE (@SortOrder > '3' OR YEAR(D.[Date]) = YEAR(GETDATE()))
AND   (@SortOrder > '2' OR MONTH(D.[Date]) = MONTH(GETDATE()))
AND   (@SortOrder > '1' OR DAY(D.[Date]) = DAY(GETDATE()))

还有一个选项,适用于2012版或更高版本,而且有点复杂,但如果您在 [Date] 列将允许SQL Server使用它:

DECLARE @Date date = GETDATE()
DECLARE @ThisMonth date = DATEFROMPARTS(YEAR(@Date), MONTH(@Date), 1)
DECLARE @ThisYear date = DATEFROMPARTS(YEAR(@Date), 1, 1)

SELECT ...
FROM ...
WHERE 
    (@SortOrder = '1' AND D.[Date] = @Date)
OR  (@SortOrder = '2' AND D.[Date] >=  @ThisMonth AND D.[Date] < DATEADD(MONTH, 1, @ThisMonth))
OR  (@SortOrder = '3' AND D.[Date] >= @ThisYear AND D.[Date] < DATEADD(YEAR, 1, @ThisYear))

You can see a live demo on rextester.

如果你想比较年、月和日,有更好的方法

select CONVERT(varchar(10), GETDATE(), 120), CONVERT(varchar(7), GETDATE(), 120), CONVERT(varchar(4), GETDATE(), 120)  
      , DATEPART(year, getdate()), DATEPART(month, getdate()),  DATEPART(day, getdate())

2018-02-22 2018-02 2018 2018 2 22