求两个日期的中间年月

[laowang134]

测试数据

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

其他类似

[快溜]

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 行受影响)

[Shawn]

;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
*/

[cs_lb]

[Quote=引用 1 楼 ssp2009 的回复:]
SQL code
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')
inser……
[/Quote]

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

[xiaoxiao8372]

楼上的牛！