SQLServer2008 在where条件中使用CASE WHEN

ronon

create table #temp
(
    id int identity(1,1),
    name varchar(20),
    startYear int,
    startMonth int
)
insert into #temp
select 'z','2010','12' union all
select 'e','2011','11' union all
select 'a','2011','2' union all
select 'b','2011','5' union all
select 'c','2011','10' union all
select 'd','2011','7'
--查询早于等于指定日期的数据
declare @condition varchar(6)
set @condition='201110'
select * from #temp A
where
 (case when LEN(A.startMonth)=1 and A.startMonth!=0 then cast((CAST(startYear as varchar(4))+'0'+cast(A.startMonth as varchar(1))) as date)
       when LEN(A.startMonth)=2 then cast(CAST(startYear as varchar(4))+cast(A.startMonth as varchar(2)) as date) end) <=cast(@condition as date)