Split Date Range into One Row Per Month in SQL Server

Split date range into one row per month in sql server

This is leap year safe and handles date ranges the other answers currently don't.

DECLARE @d TABLE(from_date DATE, to_date DATE);

INSERT @d VALUES ('2013-11-25','2013-12-05');

;WITH n(n) AS 
(
  SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(n,f,t,md,bp,ep) AS 
(
  SELECT n.n, d.from_date, d.to_date, 
    DATEDIFF(MONTH, d.from_date, d.to_date),
    DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(from_date), from_date)),
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n, 
      DATEADD(DAY, 1-DAY(from_date), from_date))))
 FROM n INNER JOIN @d AS d 
 ON d.to_date >= DATEADD(MONTH, n.n-1, d.from_date)
)
SELECT original_from_date = f, original_to_date = t, 
  new_from_date = CASE n WHEN 0  THEN f ELSE bp END,
  new_to_date   = CASE n WHEN md THEN t ELSE ep END 
FROM d WHERE md >= n
ORDER BY original_from_date, new_from_date;

Results:

original_from_date   original_to_date   new_from_date   new_to_date
------------------   ----------------   -------------   -----------
2013-11-25           2013-12-05         2013-11-25      2013-11-30
2013-11-25           2013-12-05         2013-12-01      2013-12-05

SQLFiddle demo with longer date ranges and leap years

split date range into months

The following query should do the trick. The CTE (the WITH clause) dynamically generates some Month data that we can use to join against.

declare @test table (
    userid char(1),
    project char(4),
    startdate datetime,
    enddate datetime)

insert into @test
select 'A', 'abc1', '1/1/2011', '12/31/2011'
union select 'A', 'abc2', '1/1/2011', '5/1/2011'
union select 'B', 'xyz1', '1/1/2011', '3/1/2011'

--select * from @test

;with MonthList as (
    select 
        DATEADD(month, M, '12/1/1899') as 'FirstDay',
        dateadd(day, -1, dateadd(month, M + 1, '12/1/1899')) as 'LastDay',
        DATEADD(month, M + 1, '12/1/1899') as 'FirstDayNextMonth'
    from (
        select top 3000 ROW_NUMBER() over (order by s.name) as 'M'
        from master..spt_values s) s
)

select
    t.userid, t.project, ml.FirstDay, ml.LastDay
from
    @test t
    inner join MonthList ml
        on  t.startdate < ml.FirstDayNextMonth
            and t.enddate >= ml.FirstDay

Split date range into new records for each month in Snowflake View

Idea here is to generate data-set with all dates filled in between start-date and end-date.
Used table(split_to_table(repeat(',',TIMESTAMPDIFF(day, sdate,edate)-1),',')), for same.
Once we have all dates, just select the relevant dates.

with data_cte (id,sdate,edate) as (
select * from values
('ABC','2020-12-14'::date,'2021-01-14'::date),
('XYZ','2020-12-13'::date,'2020-12-23'::date),
('DEF','2020-12-03'::date,'2021-02-03'::date)
), cte_2 as
(
select d.*,
case when sdate = edate then edate
else 
dateadd(day, index, sdate) 
end next_date,
last_day(next_date) ldate,
case when month(sdate) = month(next_date) 
AND year(sdate) = year(next_date) 
then sdate
else
date_trunc(month,next_date)
end fdate
 from data_cte d,
table(split_to_table(repeat(',',TIMESTAMPDIFF(day, sdate,edate)-1),',')) t
)
select
fdate Startdate,
next_date Enddate,
extract(year,Startdate) year,
extract(month,Startdate) month,
datediff(day,Startdate,Enddate)+1 no_of_days
 from cte_2
where (next_date = ldate
OR next_date = edate)
OR sdate = edate;

with cte as (
    select 
        employeecode, 
        startdatetime, 
        dateadd(hour, 1, datetimefromparts(year(startdatetime), month(startdatetime), day(startdatetime), datepart(hour, startdatetime), 0, 0, 0)) enddatetime
        enddatetime maxdatetime
    from mytable
    union all
    select employeecode, enddatetime, dateadd(hour, 1, enddatetime), maxdatetime
    from cte
    where enddatetime < maxdatetime
)
select employeecode, startdatetime, 
    case when enddatetime < maxdatetime then enddatetime else maxdatetime end as enddatetime
from cte

with CTE as (
      select id, dateStart as dte, dateEnd
      from t
      union all
      select id, dateadd(month, 1, dte), dateEnd
      from CTE
      where dateadd(month, 1, dte) < dateEnd
     )
select id, dte
from CTE;

select id, year(dte) * 10000 + month(dte) as yyyymm_int

select id, cast(year(dte) * 10000 + month(dte) as varchar(255)) as yyyymm

-- Generate table of 1000 numbers starting 0
with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),nmbs as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1, t0 t2, t0 t3
)
--
select Id
  , dateadd(mm, nmbs.n, tbl.PERIOD_FROM) PERIOD_FROM
  , case when ys.NextDate > tbl.PERIOD_TO then tbl.PERIOD_TO else ys.NextDate end PERIOD_TO
  , DAYS
  , tbl.PERIOD_TO originalPERIOD_TO
from [my table] tbl
join nmbs
on dateadd(mm, nmbs.n, tbl.PERIOD_FROM) <= tbl.PERIOD_TO
cross apply (select  dateadd(mm, nmbs.n + 1, tbl.PERIOD_FROM) NextDate) ys
order by ID, dateadd(mm, nmbs.n, tbl.PERIOD_FROM);