求when CASE 话语

求when CASE 语句
CheckSpecStatus BeginValidDate EndValidDate
N 2014-02-11 00:00:00.000 2014-02-18 23:59:59.000
Y 2014-02-11 00:00:00.000 2014-02-18 23:59:59.000
Y 2014-03-25 00:00:00.000 2015-12-31 23:59:59.000
N 2014-03-26 00:00:00.000 2014-04-02 23:59:59.000
Y 2015-05-19 00:00:00.000 2015-05-26 23:59:59.000
Y 2015-05-19 00:00:00.000 2015-05-26 23:59:59.000
Y 2015-05-19 00:00:00.000 2015-09-30 23:59:59.000


如果BeginValidDate EndValidDate 有效日期在今天范围之内的，CheckSpecStatus字段保持原值，否则变成N
------解决思路----------------------

--查询
SELECT CASE WHEN BeginValidDate<DATEADD(DAY,1,Today) AND EndValidDate>=Today THEN CheckSpecStatus ELSE 'N'END
	,BeginValidDate,EndValidDate
FROM TB,(SELECT CONVERT(VARCHAR(120),GETDATE(),120)Today)TodayT

--更新
UPDATE TB
SET CheckSpecStatus='N'
WHERE BeginValidDate>=DATEADD(DAY,1,CONVERT(VARCHAR(120),GETDATE(),120))
	OR EndValidDate<CONVERT(VARCHAR(120),GETDATE(),120)