求两个日期的中间年月

Question

测试数据create table #tbdate (id int identity(1,1),startdate datetime,enddate datetime)
insert into #tbdate values('2009-6-1','2009-8-2')
insert into #tbdate values('2009-5-2','2009-12-12')
insert into #tbdate values('2009-3-5','2009-8-6')
insert into #tbdate values('2009-5-8','2010-5-4')要的结果是id   startdate   enddate   yymmdiff
1   2009-6-1    2009-8-2   2009-6,2009-7,2009-8其他类似

Answer 1

create table #tbdate (id int identity(1,1),startdate datetime,enddate datetime)
insert into #tbdate values('2009-6-1','2009-8-2')
insert into #tbdate values('2009-5-2','2009-12-12')
insert into #tbdate values('2009-3-5','2009-8-6')
insert into #tbdate values('2009-5-8','2010-5-4')select *,
     yymmdiff=stuff((select ','+CONVERT(varchar(7),DATEADD(month,number,startdate),120) from #tbdate b, master..spt_values c 
            where b.id=a.id and c.type='p' and c.number<=DATEDIFF(MONTH,b.startdate,b.enddate) 
                   for xml path('')),1,1,'')
from #tbdate a/*
id          startdate               enddate                 yymmdiff
----------- ----------------------- ----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           2009-06-01 00:00:00.000 2009-08-02 00:00:00.000 2009-06,2009-07,2009-08
2           2009-05-02 00:00:00.000 2009-12-12 00:00:00.000 2009-05,2009-06,2009-07,2009-08,2009-09,2009-10,2009-11,2009-12
3           2009-03-05 00:00:00.000 2009-08-06 00:00:00.000 2009-03,2009-04,2009-05,2009-06,2009-07,2009-08
4           2009-05-08 00:00:00.000 2010-05-04 00:00:00.000 2009-05,2009-06,2009-07,2009-08,2009-09,2009-10,2009-11,2009-12,2010-01,2010-02,2010-03,2010-04,2010-05(4 行受影响)

Answer 2

;WITH cte AS
(
SELECT *
FROM (SELECT *,diff=DATEDIFF(MONTH, startdate, enddate)+1 FROM #tbdate) a
CROSS APPLY
(SELECT TOP(a.diff) yymmdiff=DATEADD(month, ROW_NUMBER() OVER(ORDER BY GETDATE())-1, a.startdate) FROM sys.columns m, sys.columns n) b
)
SELECT
id,
startdate,
enddate,
yymmdiff = STUFF((SELECT ','+CONVERT(CHAR(7),yymmdiff,120) FROM cte WHERE id=T.id FOR XML PATH('')),1,1,'')
FROM cte T
GROUP BY id, startdate, enddate
/*
1 2009-06-01 00:00:00.000 2009-08-02 00:00:00.000 2009-06,2009-07,2009-08
2 2009-05-02 00:00:00.000 2009-12-12 00:00:00.000 2009-05,2009-06,2009-07,2009-08,2009-09,2009-10,2009-11,2009-12
3 2009-03-05 00:00:00.000 2009-08-06 00:00:00.000 2009-03,2009-04,2009-05,2009-06,2009-07,2009-08
4 2009-05-08 00:00:00.000 2010-05-04 00:00:00.000 2009-05,2009-06,2009-07,2009-08,2009-09,2009-10,2009-11,2009-12,2010-01,2010-02,2010-03,2010-04,2010-05
*/

Answer 3

顶涩(*^__^*)郎 注意 运用master..spt_values 和 for xml path