title: Split date range into months (Using CTE)
body: <p>I have this query and I want an alternative for this query without using recursive CTE.
  Can Anybody please edit and update the query</p>
  <pre><code>DECLARE @d TABLE(value int ,from_date DATE, to_date DATE);
  INSERT @d VALUES (7,'2013-11-05','2013-11-25'),(8,'2013-07-05','2013-12-25'),(7,'2013-02-07','2013-04-14');
  WITH cte AS
  (SELECT value,from_date
        , to_date
        , from_date AS mo_from_date
        , DATEADD(day, day(from_date)* -1 + 1, from_date) AS bom_date
     FROM @d
  UNION ALL
  SELECT value, from_date
       , to_date
       , DATEADD(month,1,bom_date)
       , DATEADD(month,1,bom_date)
    FROM cte
   where DATEADD(month,1,mo_from_date) &lt; to_date
  )
  SELECT value,mo_from_date
       , CASE when to_date &lt; DATEADD(month,1,bom_date) THEN
             to_date
         ELSE
             DATEADD(day, -1, DATEADD(month,1,bom_date))
         END AS mo_to_date
    FROM cte
  
  
  </code></pre>
  <p>output:</p>
  <pre><code>value   mo_from_date    mo_to_date
  7   2013-11-05  2013-11-25
  8   2013-07-05  2013-07-31
  8   2013-08-01  2013-08-31
  8   2013-09-01  2013-09-30
  8   2013-10-01  2013-10-31
  8   2013-11-01  2013-11-30
  8   2013-12-01  2013-12-25
  9   2013-02-07  2013-02-28
  10  2013-03-01  2013-03-31
  11  2013-04-01  2013-04-14
  </code></pre>
  <p>I have tried below query but its not working,can anybody update this query</p>
  <pre class="lang-sql prettyprint-override"><code>;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.value, ml.FirstDay as from_date, ml.LastDay as to_date
  from
      @d t
      inner join MonthList ml
          on  t.from_date &lt; ml.FirstDayNextMonth
              and t.to_date &gt;= ml.FirstDay
  </code></pre>