Split date range into one row per month in sql server Split date range into one row per month in sql server sql sql

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 >= nORDER BY original_from_date, new_from_date;


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

SQLFiddle demo with longer date ranges and leap years

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 DateTableUNION ALLSELECT 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