求每月第一個星期日,求高手指點
------解决方案--------------------
- SQL code
GOIF OBJECT_ID('TBL')IS NOT NULLDROP TABLE TBLGOCREATE TABLE TBL(日期 DATE,)GOINSERT TBLSELECT '2012-01-01' UNION ALLSELECT '2012-02-01' UNION ALLSELECT '2012-03-01' UNION ALLSELECT '2012-04-01' UNION ALLSELECT '2012-05-01' UNION ALLSELECT '2012-06-01' UNION ALLSELECT '2012-07-01' UNION ALLSELECT '2012-08-01' UNION ALLSELECT '2012-09-01' UNION ALLSELECT '2012-10-01' UNION ALLSELECT '2012-11-01' UNION ALLSELECT '2012-12-01'select case when day(dateadd(dd,7-datepart(WEEKDAY,日期)+1,日期))>7 then 日期else dateadd(dd,7-datepart(WEEKDAY,日期)+1,日期) end as 星期日 from tbl/*星期日2012-01-012012-02-052012-03-042012-04-012012-05-062012-06-032012-07-012012-08-052012-09-022012-10-072012-11-042012-12-02*/
------解决方案--------------------
- SQL code
select convert(varchar(7),dt,120) [mm] , min(dt) dt from(select dateadd(dd,num,'2012-01-01') dtfrom (select distinct num = (m.number+n.number) from master..spt_values m,master..spt_values n where m.type='P' and n.type='P') twhere dateadd(dd,num,'2012-01-01')<='2012-12-31' and datepart(weekday,dateadd(dd,num,'2012-01-01')) = 1) kgroup by convert(varchar(7),dt,120)order by convert(varchar(7),dt,120)/*mm dt ------- ------------------------------------------------------ 2012-01 2012-01-01 00:00:00.0002012-02 2012-02-05 00:00:00.0002012-03 2012-03-04 00:00:00.0002012-04 2012-04-01 00:00:00.0002012-05 2012-05-06 00:00:00.0002012-06 2012-06-03 00:00:00.0002012-07 2012-07-01 00:00:00.0002012-08 2012-08-05 00:00:00.0002012-09 2012-09-02 00:00:00.0002012-10 2012-10-07 00:00:00.0002012-11 2012-11-04 00:00:00.0002012-12 2012-12-02 00:00:00.000（所影响的行数为 12 行）*/