declare @year int,@d varchar(10) set @year=2009 set @d=ltrim(@year)+'-01-01' SELECT ltrim(m)+'月第一个星期一' as col DATEADD(wk, DATEDIFF(wk,'',DATEADD(dd,6-DAY(@d), @d)),'') from (select 1 as m union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10 union select 11 union select 12) t
;with t as ( select top 365 d= dateadd(dd,number,'2010-01-01') from master..spt_values where type='p' ) select d from( select *,rn=row_number()over(partition by month(d) order by d) from t where datepart(w,d)=2)a where rn=1 /* 2010-01-04 00:00:00.000 2010-02-01 00:00:00.000 2010-03-01 00:00:00.000 2010-04-05 00:00:00.000 2010-05-03 00:00:00.000 2010-06-07 00:00:00.000 2010-07-05 00:00:00.000 2010-08-02 00:00:00.000 2010-09-06 00:00:00.000 2010-10-04 00:00:00.000 2010-11-01 00:00:00.000 2010-12-06 00:00:00.000*/
declare @date datetime set @date='2010-01-01'
select min(case when datepart(weekday,DATEADD(day,b.number,mon))=1 then CONVERT(nvarchar(10),DATEADD(day,b.number,mon),120) end) as 日期 from ( select DATEADD(month,number,@date) as mon from master..spt_values where number<=12 and type='p' )a cross join(select number from master..spt_values where number<=31 and type='p' and number>0)b where datepart(weekday,DATEADD(day,b.number,mon))=1 group by LEFT(mon,7) order by 日期
declare @date datetime set @date='2010-01-01' select CONVERT(varchar(7),rq,120) as 月份,min(rq) as 日期 from( select case when datepart(weekday,DATEADD(day,b.number,mon))=1 then CONVERT(nvarchar(10),DATEADD(day,b.number,mon),120) end as rq from ( select DATEADD(month,number,@date) as mon from master..spt_values where number<=11 and type='p' )a cross join(select number from master..spt_values where number<=31 and type='p')b where datepart(weekday,DATEADD(day,b.number,mon))=1)a group by CONVERT(varchar(7),rq,120) order by 日期
declare @t datetime
set @t='2009-1-1'select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,@t),@t)), 0) /*-----------------------
2009-01-05 00:00:00.000(1 行受影响)*/
declare @t datetime
set @t='2009-2-1'select DATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,@t),@t)), 0)
/*-----------------------
2009-02-02 00:00:00.000(1 行受影响)
*/
set @year=2009
set @d=ltrim(@year)+'-01-01'
SELECT
ltrim(m)+'月第一个星期一' as col
DATEADD(wk, DATEDIFF(wk,'',DATEADD(dd,6-DAY(@d), @d)),'')
from
(select 1 as m union select 2 union select 3 union select 4 union select 5 union select 6
union select 7 union select 8 union select 9 union select 10 union select 11 union select 12) t
(
select top 365 d= dateadd(dd,number,'2010-01-01')
from master..spt_values where type='p'
)
select d from(
select *,rn=row_number()over(partition by month(d) order by d) from t
where datepart(w,d)=2)a where rn=1
/*
2010-01-04 00:00:00.000
2010-02-01 00:00:00.000
2010-03-01 00:00:00.000
2010-04-05 00:00:00.000
2010-05-03 00:00:00.000
2010-06-07 00:00:00.000
2010-07-05 00:00:00.000
2010-08-02 00:00:00.000
2010-09-06 00:00:00.000
2010-10-04 00:00:00.000
2010-11-01 00:00:00.000
2010-12-06 00:00:00.000*/
set @date='2010-01-01'
select min(case when datepart(weekday,DATEADD(day,b.number,mon))=1 then
CONVERT(nvarchar(10),DATEADD(day,b.number,mon),120) end) as 日期
from
(
select DATEADD(month,number,@date) as mon
from master..spt_values where number<=12 and type='p'
)a cross join(select number from master..spt_values where number<=31 and type='p' and number>0)b
where datepart(weekday,DATEADD(day,b.number,mon))=1
group by LEFT(mon,7)
order by 日期
/*
日期
2010-01-04
2010-02-08
2010-03-08
2010-04-05
2010-05-03
2010-06-07
2010-07-05
2010-08-02
2010-09-06
2010-10-04
2010-11-08
2010-12-06
*/
set @date='2010-01-01'
select CONVERT(varchar(7),rq,120) as 月份,min(rq) as 日期
from(
select case when datepart(weekday,DATEADD(day,b.number,mon))=1 then
CONVERT(nvarchar(10),DATEADD(day,b.number,mon),120) end as rq
from
(
select DATEADD(month,number,@date) as mon
from master..spt_values where number<=11 and type='p'
)a cross join(select number from master..spt_values where number<=31 and type='p')b
where datepart(weekday,DATEADD(day,b.number,mon))=1)a
group by CONVERT(varchar(7),rq,120)
order by 日期
/*
月份 日期
2010-01 2010-01-04
2010-02 2010-02-01
2010-03 2010-03-01
2010-04 2010-04-05
2010-05 2010-05-03
2010-06 2010-06-07
2010-07 2010-07-05
2010-08 2010-08-02
2010-09 2010-09-06
2010-10 2010-10-04
2010-11 2010-11-01
2010-12 2010-12-06
*/不好意思,刚才错了
declare @year varchar(4)
set @year = '2010'select monday =dateadd(d,
(9-datepart(w,dateadd(m,number,@year)))%7
,dateadd(m,number,@year))
from master..spt_values
where number between 0 and 11
and type = 'p' monday
------------------------------------------------------
2010-01-04 00:00:00.000
2010-02-01 00:00:00.000
2010-03-01 00:00:00.000
2010-04-05 00:00:00.000
2010-05-03 00:00:00.000
2010-06-07 00:00:00.000
2010-07-05 00:00:00.000
2010-08-02 00:00:00.000
2010-09-06 00:00:00.000
2010-10-04 00:00:00.000
2010-11-01 00:00:00.000
2010-12-06 00:00:00.000(所影响的行数为 12 行)