SQL Split date range

Question

I need to split date range , for example:

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

I use this query to split daterange. But for some reason i get an extra row with the following month. which is wrong. How can i fix it?

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(MONTH, n.n+1, DATEADD(DAY, 0-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;

But for some reason ,

I ran the query and got two rows with new date as 25 Nov to 30 Nov and 1 dec to 5 dec which is what you want. I do not understand what exactly is the problem here. — Abhay Chauhan, Aug 28 '15 at 05:52
Try adding output of the query and highlighting the issue you are facing. — Abhay Chauhan, Aug 28 '15 at 05:52
Unrelated but: you don't need a `;` in front of the `with` if you have already properly terminated the previous statement with a `;` — , Aug 28 '15 at 06:05
@a_horse_with_no_name I personally like to put one before the with. At least I know it is always there in case something change before it and the ; if forgotten. — Julien Vavasseur, Aug 28 '15 at 06:49
@JulienVavasseur: sticking to this habit (which looks _really_ strange in the eyes of non SQL Server users) leads to questions like [this](http://stackoverflow.com/questions/11491240/how-to-create-temp-table-with-select-into-temptable-from-cte-query) or [this](http://stackoverflow.com/questions/26670414/how-use-with-in-subquery-ms-sql). — , Aug 28 '15 at 06:52
I understand. However the problem in your links is not just about removing the ;. The questions are more about CTE declaration in a subquery whether there is a; or not. — Julien Vavasseur, Aug 28 '15 at 07:02
@Misha - correct me if I am wrong, do want to remove the extra `2013-11-25 2013-12-05` from the 2nd row? — Abhishek, Aug 28 '15 at 07:11

score 0 · Answer 1 · answered Aug 28 '15 at 08:04

You can also try to use recursive way to do it:

    WITH CTE as 
    (
    SELECT from_date,To_date,from_date as BEG_D, 
             CASE WHEN DATEADD(DAY,-DAY(DATEADD(MONTH,1, d.from_date)),
                                    DATEADD(MONTH,1, d.from_date))
                       < To_date 
                       THEN DATEADD(DAY,-DAY(DATEADD(MONTH,1, d.from_date)),
                                        DATEADD(MONTH,1, d.from_date))
                       ELSE To_Date END as END_D
      FROM D
      UNION ALL 
      SELECT from_date,To_date,DATEADD(DAY,1,END_D) as BEG_D, 
             CASE WHEN DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1,END_D)))
                       < To_date 
                       THEN DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1,END_D)))
                       ELSE To_Date END as END_D
      FROM CTE WHERE END_D < To_Dat
      )
      SELECT * FROM CTE ORDER BY from_date,END_D;

SQLFiddle demo

SQL Split date range

1 Answers1