sql - 获取 SQL 中两个日期之间月份的开始日期和结束日期？

Question

我有以下函数，它应该返回两个月之间的月份的开始和结束日期，问题是因为这个月是 28 天，函数正在 28 天计算所有即将到来的月份基础因此返回以下错误值。

StartDate   EndDate
-----------------------   
2013-02-01  2013-02-28 
2013-03-01  2013-03-28 
2013-04-01  2013-04-28 



declare @sDate datetime,
        @eDate datetime;

select  @sDate = '2013-02-25',
        @eDate = '2013-04-25';

;with months as
(
  select DATEADD(mm,DATEDIFF(mm,0,@sDate),0) StartDate, 
  DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0)) EndDate

  union all
  select dateadd(mm, 1, StartDate),
    dateadd(mm, 1, EndDate)
  from months
  where dateadd(mm, 1, StartDate)<=  @eDate
)

select * from months

我如何修改它以返回正确的日期？

Answer 1

试试这个；

declare @sDate datetime,
        @eDate datetime

select  @sDate = '2013-02-25',
        @eDate = '2013-04-25'

;with cte as (
  select convert(date,left(convert(varchar,@sdate,112),6) + '01') startDate,
         month(@sdate) n
  union all
  select dateadd(month,n,convert(date,convert(varchar,year(@sdate)) + '0101')) startDate,
        (n+1) n
  from cte
  where n < month(@sdate) + datediff(month,@sdate,@edate)
)
select startdate, dateadd(day,-1,dateadd(month,1,startdate)) enddate
from cte

FIDDLE DEMO

|  STARTDATE |    ENDDATE |
---------------------------
| 2013-02-01 | 2013-02-28 |
| 2013-03-01 | 2013-03-31 |
| 2013-04-01 | 2013-04-30 |