Insert Months between 2 dates into a temp table

• francissvk

  SSChasing Mays

  Points: 620

  More actions

  June 21, 2011 at 12:24 am

  #242422

  Hi all,

  I want to insert month and year between 2 dates into a temp table. How can i

  achieve it?

  consider From Date as 10-June-2010 and "To date" as 1-May-2011

  Then I want to insert (11 Months) the Month between "June" 2010 to "May" 2011 into a temp table.

  Thanks

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 21, 2011 at 2:00 am

  #1341671

  You need a tally table (or tally cte) for this:

  DECLARE @FromDate DATETIME, @ToDate DATETIME

  SELECT @FromDate = '10-June-2010', @ToDate = '1-May-2011'

  -- It's unclear in your requirements if you need to include June 2010 in the results

  -- because it would make the query return 12 months, not 11.

  SELECT @FromDate = CONVERT(datetime,CONVERT(char(6), @FromDate, 112) + '01',112)

  ;WITH E1(N) AS (

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

  SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

  ),

  E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b),

  E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b),

  cteTally(N) AS (

  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

  FROM E3

  ),

  months (monthStartDate) AS (

  SELECT DATEADD(month, N, 0)

  FROM cteTally

  )

  SELECT *

  FROM months

  WHERE monthStartDate BETWEEN @FromDate AND @ToDate

  Hope this helps

  Gianluca

  -- Gianluca Sartori