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;

Results:

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