select the same day each month for the next 5 years using SQL?

How do i select the same day each month for the next 5 years using SQL?

eg:

  • Comma separated output based on varchar field
  • comparable varchar “arrays” in seperate fields but on same row
  • View joining with stored Procedure results
  • How to determine the schema of a REST service?
  • Get multiple results from sp_executesql
  • Re-use aliased field in SQL SELECT statement
    1. 05 Jan 2013
    2. 05 Feb 2013
    3. 05 Mar 2013

    I have tried:

    select dateadd(day,-day(dateadd(month,1,current_timestamp)) , dateadd(month,1,current_timestamp) )
    

    2 Solutions collect form web for “select the same day each month for the next 5 years using SQL?”

    How about something like

    DECLARE @StartDate DATETIME = '05 Jan 2013',
            @YearsAdded INT = 5
    
    ;WITH Dates AS (
            SELECT  @StartDate [Date]
            UNION ALL
            SELECT  DATEADD(MONTH,1,[Date])
            FROM    Dates
            WHERE   DATEADD(MONTH,1,[Date]) <= DATEADD(YEAR,@YearsAdded,@StartDate)
    )
    SELECT  *
    FROM    Dates
    OPTION (MAXRECURSION 0)
    

    SQL Fiddle DEMO

    This is perfect for a tally table

    MONTHLY

    --========================================================================================================================
    /* tally                                                                                                                */
    --========================================================================================================================
    
    ;WITH parms AS (SELECT
         YearSpan      = 5 
        ,DayOfTheMonth = 5
    )
    
    ,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
    )
    
    , Tally(N) AS (
        SELECT 0 UNION
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM E1 a, E1 b
    )
    
    --========================================================================================================================
    /* projection                                                                                                           */
    --========================================================================================================================
    
    SELECT 
        ResultDate = DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, t.N, GETDATE())), (p.DayOfTheMonth - 1))
    FROM parms p
    CROSS JOIN Tally t
    WHERE DATEADD(mm, t.N, GETDATE()) < DATEADD(yy, p.YearSpan, GETDATE())
    

    QUARTERLY (uses the same “tally” code block as the MONTHLY so I’ve omitted the code for it)

    --========================================================================================================================
    /* tally                                                                                                                */
    --========================================================================================================================
    
    -- [code omitted]
    
    --========================================================================================================================
    /* projection                                                                                                           */
    --========================================================================================================================
    
    SELECT 
        ResultDate = DATEADD(qq, DATEDIFF(qq, 0, DATEADD(qq, t.N, GETDATE())), (p.DayOfTheMonth - 1))
    FROM parms p
    CROSS JOIN Tally t
    WHERE DATEADD(qq, t.N, GETDATE()) < DATEADD(yy, p.YearSpan, GETDATE())
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.