Split date range into one row per month in sql server

Question

I have a table with two column called "from_date" and "to_date"

    the table look like:-

enter image description here

I want result like:-

from_date            to_date   
-----------         ------------  
2013-11-25           2013-11-30
2013-12-01           2013-12-05

That date is splits from 2013-11-25 to 2013-11-30 and another date split from 2013-12-01 to 2013-12-05... Is it possible to split like this ?

The following is *almost* the same. It works only for full months but it should be trivial to modify it to work on partial months as well: http://stackoverflow.com/questions/7218526/split-date-range-into-months — Heinzi, Nov 28 '13 at 15:40
If the dates span 3 months e.g. `2013-11-25` to `2014-02-05` should this split into 3 rows or 4? — GarethD, Nov 28 '13 at 16:15

Aaron Bertrand · Accepted Answer · 2015-12-29T16:47:39.200

10

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

edited Dec 29 '15 at 16:47

answered Nov 28 '13 at 16:51

Aaron Bertrand

272,866
37
466
490

If the first from_date is for a month that follows a month with less than 31 days, your solution fails to correctly find the last day of the month. Start with October rather than November. October follows September, and September has less than 31 days. – Kennah Dec 29 '15 at 16:23
I didn't find this. Martijn Vermunt did. http://stackoverflow.com/q/34514361/2471473 – Kennah Dec 29 '15 at 16:25
@AaronBertrand, you sir, ROCK! – Kennah Dec 29 '15 at 17:22
1

What if i have time with my date? I tried the above query when i have time defined with date, but splitting into months works wrong – Misha Groiser Feb 20 '16 at 21:55

score 4 · Answer 2 · answered Nov 28 '13 at 18:29

If you are operating in a dimensional data warehouse, utilize the date dimension. Otherwise, use CTE.

WITH cte AS
(SELECT from_date
      , to_date
      , from_date AS mo_from_date
      , DATEADD(day, day(from_date)* -1 + 1, from_date) AS bom_date
   FROM DateTable
UNION ALL
SELECT from_date
     , to_date
     , DATEADD(month,1,bom_date)
     , DATEADD(month,1,bom_date)
  FROM cte
 where DATEADD(month,1,mo_from_date) < to_date
)
SELECT mo_from_date
     , CASE when to_date < DATEADD(month,1,bom_date) THEN
           to_date
       ELSE
           DATEADD(day, -1, DATEADD(month,1,bom_date))
       END AS mo_to_date
  FROM cte

Split date range into one row per month in sql server

2 Answers2

Linked