declare @dt varchar(7) set @dt='2011-06' --如要判断别的月份改这里 select count(*) from master..spt_values where type='p' and datepart(dw,dateadd(d,number,@dt+'-01'))=4 and convert(varchar(7),dateadd(d,number,'2011-06-01'),120)=@dt /* ----------- 5(1 行受影响) */
select sum(case when datename(weekday,date) = N'星期三' then 1 else 0 end) from tb where convert(varchar(6),date,112) = '201106'
有个小错,纠正: declare @dt varchar(7) set @dt='2011-06' select count(*) from master..spt_values where type='p' and datepart(dw,dateadd(d,number,@dt+'-01'))=4 and convert(varchar(7),dateadd(d,number,@dt+'-01'),120)=@dt /* ----------- 5(1 行受影响) */
select count(*) from master..spt_values where type='p' and datename(weekday,dateadd(day,number,'2011-06-01'))='星期三' and dateadd(day,number,'2011-06-01') between '2011-06-01' and '2011-06-30' ----------- 5(所影响的行数为 1 行)
select sum(case when datename(weekday,date) = '星期三' then 1 else 0 end) from tb where convert(varchar(7),date,120) = '2011-06'
set @dt='2011-06' --如要判断别的月份改这里
select count(*) from master..spt_values
where type='p' and datepart(dw,dateadd(d,number,@dt+'-01'))=4 and convert(varchar(7),dateadd(d,number,'2011-06-01'),120)=@dt
/*
-----------
5(1 行受影响)
*/
select sum(case when datename(weekday,date) = N'星期三' then 1 else 0 end)
from tb
where convert(varchar(6),date,112) = '201106'
declare @dt varchar(7)
set @dt='2011-06'
select count(*) from master..spt_values
where type='p' and datepart(dw,dateadd(d,number,@dt+'-01'))=4 and convert(varchar(7),dateadd(d,number,@dt+'-01'),120)=@dt
/*
-----------
5(1 行受影响)
*/
where type='p' and
datename(weekday,dateadd(day,number,'2011-06-01'))='星期三'
and dateadd(day,number,'2011-06-01') between '2011-06-01' and '2011-06-30'
-----------
5(所影响的行数为 1 行)
sum(case when datename(weekday,date) = '星期三' then 1 else 0 end)
from
tb
where
convert(varchar(7),date,120) = '2011-06'