go
if object_id('test')is not null
drop table test
go
create table test(
[date] date
)
go
insert test
select '2012-04-01' union all
select '2012-04-02' union all
select '2012-04-03' union all
select '2012-04-04' union all
select '2012-04-05' union all
select '2012-04-06' union all
select '2012-04-07' union all
select '2012-04-08' union all
select '2012-04-09' union all
select '2012-04-10' union all
select '2012-04-11' union all
select '2012-04-12' union all
select '2012-04-13' union all
select '2012-04-14' union all
select '2012-04-15' union all
select '2012-04-16' union all
select '2012-04-17' union all
select '2012-04-18' union all
select '2012-04-19' union all
select '2012-04-20' union all
select '2012-04-21' union all
select '2012-04-22' union all
select '2012-04-23' union all
select '2012-04-24' union all
select '2012-04-25' union all
select '2012-04-26' union all
select '2012-04-27' union all
select '2012-04-28' union all
select '2012-04-29' union all
select '2012-04-30'

select * from (
select [date] 日期,
 case DATEPART(W,[date])-1 
           when 1 then '星期一'
           when 2 then '星期二'
           when 3 then '星期三'
           when 4 then '星期四'
           when 5 then '星期五'
           when 6 then '星期六' else '星期日' end as 星期
from test)a where (日期 between '2012-04-01' and '2012-04-30')
and 星期='星期日'
/*
日期    星期
2012-04-01    星期日
2012-04-08    星期日
2012-04-15    星期日
2012-04-22    星期日
2012-04-29    星期日
*/