select id = identity(int,0,1) into #t from sysobjects declare @ny char(7) set @ny = '2006-11' select * from ( select rq =dateadd(d,id,@ny+'-01') from #t where id < 31) a where datepart(dw,a.rq)=1drop table #trq ------------------------------------------------------ 2006-11-05 00:00:00.000 2006-11-12 00:00:00.000 2006-11-19 00:00:00.000 2006-11-26 00:00:00.000
declare @dt datetime, @begDt datetime, @endDt datetime select @dt='2006-11-10', @begDt=convert(char(8), @dt, 120)+'01', @endDt=dateadd(day, -1, convert(char(8), dateadd(month, 1, @dt), 120)+'01')declare @re int set @re=0while @begDt<=@endDt begin if (datepart(weekday, @begDt)-1 )%7=0 set @re=@re+1
set @begDt=dateadd(day, 1, @begDt) endselect @re
declare @dt datetime, @begDt datetime, @endDt datetime select @dt='2006-10-10', @begDt=convert(char(8), @dt, 120)+'01', @endDt=dateadd(day, -1, convert(char(8), dateadd(month, 1, @dt), 120)+'01')declare @re int set @re=0while @begDt<=@endDt begin if ( datepart(weekday, @begDt)+@@datefirst-1 )%7=0 set @re=@re+1
set @begDt=dateadd(day, 1, @begDt) endselect @re
select top 31 id = identity(int,0,1) into #t from sysobjects declare @ny char(7) set @ny = '2006-11' select * from ( select rq =dateadd(d,id,@ny+'-01') from #t ) a where convert(char(7),a.rq,120)=@ny and datepart(dw,a.rq)=8-@@DATEFIRSTdrop table #t
set @mm=getdate()
select (DateDiff(day, @mm, DateAdd(month, 1, @mm))+(5+datepart(weekday,@mm))%7)/7
from sysobjects
declare @ny char(7)
set @ny = '2006-11'
select * from (
select rq =dateadd(d,id,@ny+'-01') from #t where id < 31) a
where datepart(dw,a.rq)=1drop table #trq
------------------------------------------------------
2006-11-05 00:00:00.000
2006-11-12 00:00:00.000
2006-11-19 00:00:00.000
2006-11-26 00:00:00.000
declare @month char(2)
select @year = '2006',@month = '07'
select (datediff(day,@year+'-'+@month+'-01',dateadd(month,1,@year+'-'+@month+'-01')) + (5 + datepart(weekday,@year+'-'+@month+'-01'))%7)/7
select @dt='2006-11-10',
@begDt=convert(char(8), @dt, 120)+'01',
@endDt=dateadd(day, -1, convert(char(8), dateadd(month, 1, @dt), 120)+'01')declare @re int
set @re=0while @begDt<=@endDt
begin
if (datepart(weekday, @begDt)-1 )%7=0
set @re=@re+1
set @begDt=dateadd(day, 1, @begDt)
endselect @re
select @dt='2006-10-10',
@begDt=convert(char(8), @dt, 120)+'01',
@endDt=dateadd(day, -1, convert(char(8), dateadd(month, 1, @dt), 120)+'01')declare @re int
set @re=0while @begDt<=@endDt
begin
if ( datepart(weekday, @begDt)+@@datefirst-1 )%7=0
set @re=@re+1
set @begDt=dateadd(day, 1, @begDt)
endselect @re
from sysobjects
declare @ny char(7)
set @ny = '2006-11'
select * from (
select rq =dateadd(d,id,@ny+'-01') from #t ) a
where convert(char(7),a.rq,120)=@ny and datepart(dw,a.rq)=8-@@DATEFIRSTdrop table #t