declare @sdate datetime
declare @edate datetime
set @sdate = convert(datetime, '2014/01/02');
set @edate = convert(datetime, '2014/12/15');
WITH dt(idx, s, e) as(
select 1,
@sdate,
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, @sdate)+1,0))
union all
select idx+1,
DATEADD(mm, DATEDIFF(m,0,dateadd(m, 1, s)),0),
case when dateadd(m, 1, e) < @edate
then DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, 1, e))+1,0))
else @edate
end
from dt
where dateadd(m, 1, s) < @edate
)
select *
from dt;
-------
1 2014-01-02 00:00:00.000 2014-01-31 23:59:59.000
2 2014-02-01 00:00:00.000 2014-02-28 23:59:59.000
3 2014-03-01 00:00:00.000 2014-03-31 23:59:59.000
4 2014-04-01 00:00:00.000 2014-04-30 23:59:59.000
5 2014-05-01 00:00:00.000 2014-05-31 23:59:59.000
6 2014-06-01 00:00:00.000 2014-06-30 23:59:59.000
7 2014-07-01 00:00:00.000 2014-07-31 23:59:59.000
8 2014-08-01 00:00:00.000 2014-08-31 23:59:59.000
9 2014-09-01 00:00:00.000 2014-09-30 23:59:59.000
10 2014-10-01 00:00:00.000 2014-10-31 23:59:59.000
11 2014-11-01 00:00:00.000 2014-11-30 23:59:59.000
12 2014-12-01 00:00:00.000 2014-12-15 00:00:00.000
