[T-SQL] 利用起訖查詢月區間

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