Post reply

Insert Months between 2 dates into a temp table

  • 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

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply