SQL date range breaking into months

Question

I have a sql table that has the following

ID     StartDate     EndDate 
10     2015-12-01    2016-05-31
15     2016-01-05    2016-07-04
20     2016-02-10    2016-08-09

I need to break down the months like so...

ID     StartDate     EndDate
10     2015-12-01    2015-12-31
10     2016-01-01    2016-01-31
10     2016-02-01    2016-02-29
10     2016-03-01    2016-03-31
10     2016-04-01    2016-04-30
10     2016-05-01    2016-05-31
15     2016-01-05    2016-02-04
15     2016-02-05    2016-03-04
15     2016-03-05    2016-04-04
15     2016-04-05    2016-05-04
15     2016-05-05    2016-06-04
15     2016-06-05    2016-07-04
etc

I'm new to SQL so an example would be very helpful

Answer 1

This will work on SQL Server 2012 and up; the EOMONTH function does not exist on earlier versions.

DECLARE @table TABLE (ID INT, StartDate DATE, EndDate DATE)
DECLARE @outtable TABLE (ID INT, StartDate DATE, EndDate DATE)
DECLARE @ID INT
DECLARE @StartDate DATE
DECLARE @Date1 DATE
DECLARE @Date2 DATE
DECLARE @EndDate DATE

INSERT INTO @table VALUES
(10,'2015-12-01','2016-05-31')
,(15,'2016-01-05','2016-07-04')
,(20,'2016-02-10','2016-08-09')

DECLARE tablecursor CURSOR FOR
SELECT * FROM @table

OPEN tablecursor
FETCH NEXT FROM tablecursor INTO @ID, @StartDate, @EndDate
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Date1 = @StartDate
    SET @Date2 = EOMONTH(@Date1) 
    WHILE @Date1 < @EndDate
    BEGIN
        PRINT CONVERT(VARCHAR,@ID) + ' ' +  CONVERT(VARCHAR,@Date1) + ' ' + CONVERT(VARCHAR,@Date2)
        INSERT INTO @outtable
        SELECT @ID, @Date1, @Date2
        SET @Date1 = DATEADD(DAY,1,@Date2)
        SET @Date2 = EOMONTH(@Date1)
        IF @Date2 > @EndDate 
        BEGIN
            SET @Date2 = @EndDate
        END
    END
    FETCH NEXT FROM tablecursor INTO @ID, @StartDate, @EndDate
END

SELECT * FROM @outtable

CLOSE tablecursor
DEALLOCATE tablecursor

Answer 2

Calendar

recommended if you have persistent Calendar/DateRanges table

declare @datebegin date = '20140101'

;with cteCalendar as
(
  select
    c.period_start,
    dateadd(dd, -1, dateadd(mm, 1, c.period_start)) as period_end
  from
  (
    select top 100
      dateadd(mm, row_number() over(order by sc.object_id)-1, @datebegin) as period_start
    from sys.columns sc
    order by period_start
  ) c
),
cteData as
(
  select cast(10 as int) as id, cast('20151201' as date) as StartDate, cast('20160531' as date) as EndDate
  union all 
  select 15, '20160105', '20160704'
  union all 
  select 25, '20160210', '20160809'
),
cteDataEx as
(
  select d.id, d.StartDate, d.EndDate, datepart(dd, d.StartDate)-1 as DateOffset
  from cteData d
)
select
  d.id,
  dateadd(dd, d.DateOffset, c.period_start) as StartDate, 
  dateadd(dd, d.DateOffset, c.period_end) as EndDate
from cteDataEx d
inner join cteCalendar c on c.period_start <= d.EndDate and c.period_end >= d.StartDate
where dateadd(dd, d.DateOffset, c.period_end) <= d.EndDate
order by id, StartDate

Actually I did not notice at the beginning that periods may start and end not at 1st day of month, so had to append some calculations after completion of the whole script. Later I realized that <= >= date filter produces unnecessary last row which overflows original date range high bound. So had to append final filter and after that modification don't like this approach totally )) May be some enhancements can be applied but I'm not interested in. Lots of ways to accomplish this task.

Additional information about nature and purpose of periods given may alter relevance and applicability of different approaches

Recursion

no extra data required but recursion can be slow if date ranges can be wide enough.

;with cteData as
(
  select cast(10 as int) as id, cast('20151201' as date) as StartDate, cast('20160531' as date) as EndDate
  union all 
  select 15, '20160105', '20160704'
  union all 
  select 25, '20160210', '20160809'
),
ctePeriods as
(
  select
    d.id, 
    d.StartDate, 
    dateadd(dd, -1, dateadd(mm, 1, d.StartDate)) as EndDate, 
    d.EndDate as _EndDate
  from cteData d

  union all

  select
    p.id, 
    dateadd(mm, 1, p.StartDate),
    dateadd(dd, -1, dateadd(mm, 2, p.StartDate)), 
    p._EndDate
  from ctePeriods p
  where p.EndDate < p._EndDate
)
select p.id, p.StartDate, p.EndDate
from ctePeriods p
order by id, startdate

Answer 3

this code generate the rage of months, inclute leap year, but I don't undestand your need so explain better

create table #dia_meses
(mes int,
messtr varchar(2),
dia_final varchar(2))

insert into #dia_meses values(1,'01','31')
insert into #dia_meses values(2,'02','29')
insert into #dia_meses values(3,'03','31')
insert into #dia_meses values(4,'04','30')
insert into #dia_meses values(5,'05','31')
insert into #dia_meses values(6,'06','30')
insert into #dia_meses values(7,'07','31')
insert into #dia_meses values(8,'08','31')
insert into #dia_meses values(9,'09','30')
insert into #dia_meses values(10,'10','31')
insert into #dia_meses values(11,'11','30')
insert into #dia_meses values(12,'12','31')

declare @year varchar(4)
declare @contador int
set @year =convert(varchar,DATEPART(YEAR,GETDATE()))
set @contador =convert(varchar,DATEPART(month,GETDATE()))
    declare @dataIni datetime
    declare @datafim datetime
    set @dataIni=(select @year+'-'+messtr+'-01' from #dia_meses where mes=@contador)
    --pulo do gato ano bissexto
    if(@contador=2)
    begin
        if(select ISDATE(@year+'-'+messtr+'-'+dia_final) from #dia_meses where mes=@contador)=0
        begin
            set @datafim=(select @year+'-'+messtr+'-28' from #dia_meses where mes=@contador)
        end
        else--ano bissexto
        begin
            set @datafim=(select @year+'-'+messtr+'-'+dia_final from #dia_meses where mes=@contador)
        end

    end
    else
    begin
        set @datafim=(select @year+'-'+messtr+'-'+dia_final from #dia_meses where mes=@contador)
    end
        print @dataIni
        print @dataFim