日期:2014-05-18  浏览次数:20478 次

求每月第一個星期日,求高手指點
求每月第一個星期日,求高手指點

------解决方案--------------------
SQL code

GO
IF OBJECT_ID('TBL')IS NOT NULL
DROP TABLE TBL
GO
CREATE TABLE TBL(
日期 DATE,
)
GO
INSERT TBL
SELECT '2012-01-01' UNION ALL
SELECT '2012-02-01' UNION ALL
SELECT '2012-03-01' UNION ALL
SELECT '2012-04-01' UNION ALL
SELECT '2012-05-01' UNION ALL
SELECT '2012-06-01' UNION ALL
SELECT '2012-07-01' UNION ALL
SELECT '2012-08-01' UNION ALL
SELECT '2012-09-01' UNION ALL
SELECT '2012-10-01' UNION ALL
SELECT '2012-11-01' UNION ALL
SELECT '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-01
2012-02-05
2012-03-04
2012-04-01
2012-05-06
2012-06-03
2012-07-01
2012-08-05
2012-09-02
2012-10-07
2012-11-04
2012-12-02
*/

------解决方案--------------------
SQL code
select convert(varchar(7),dt,120) [mm] , min(dt) dt from
(
select 
    dateadd(dd,num,'2012-01-01')  dt
from 
    (select distinct num = (m.number+n.number) from master..spt_values m,master..spt_values n where m.type='P' and n.type='P') t
where
    dateadd(dd,num,'2012-01-01')<='2012-12-31' and datepart(weekday,dateadd(dd,num,'2012-01-01')) = 1
) k
group by convert(varchar(7),dt,120)
order by convert(varchar(7),dt,120)

/*
mm      dt                                                     
------- ------------------------------------------------------ 
2012-01 2012-01-01 00:00:00.000
2012-02 2012-02-05 00:00:00.000
2012-03 2012-03-04 00:00:00.000
2012-04 2012-04-01 00:00:00.000
2012-05 2012-05-06 00:00:00.000
2012-06 2012-06-03 00:00:00.000
2012-07 2012-07-01 00:00:00.000
2012-08 2012-08-05 00:00:00.000
2012-09 2012-09-02 00:00:00.000
2012-10 2012-10-07 00:00:00.000
2012-11 2012-11-04 00:00:00.000
2012-12 2012-12-02 00:00:00.000

(所影响的行数为 12 行)
*/