Autopopulate SQL field with mmyyyy dates for the next 20 years

I am wondering if I can get some help with a statement

I am trying to populate a field named monthyear  with new records in the format as below:




is there a quick way of doing this with a bit of programming and an insert statement?

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SET @I = 0

DECLARE @Dates Table (
      MonthYear char(6)

WHILE @I < (20 * 12)
            INSERT INTO @Dates(MonthYear)
            SELECT RIGHT('0' + CAST(DATEPART(Month, DATEADD(M, @I, GETDATE())) AS varchar(2)), 2) + CAST(YEAR(DATEADD(Month, @I, GETDATE())) AS char(4))
            SET @I = @I + 1
            IF @I < (20 * 12)


SELECT MonthYear
FROM @Dates
PortletPaulEE Topic AdvisorCommented:
Not entirely sure why you would want a 6 character string starting with zero padded month as a field in a table without other fields to help you produce a sensible (date sequenced) sort order.

An approach using a recursive recursive CTE
DECLARE @start int
DECLARE @end int
SET @start = 2010
SET @end = 2030
;with m12( Mno ) as (
  select  1 union all select  2 union all select  3 union all
  select  4 union all select  5 union all select  6 union all
  select  7 union all select  8 union all select  9 union all
  select 10 union all select 11 union all select 12
, Nums( Number ) as (
    select @start as Number
    union all
    select Number + 1
    from Nums
    where Number < @end
-- & you can the result insert into a table 
        right('00' + cast(Mno as varchar(2)) + cast(Number as varchar(4)) as monthyear
      , mno as MonthNumber
      , Number as YearNumber
from Nums
cross join m12
option (maxrecursion 1000)

Open in new window

Anthony PerkinsCommented:
SELECT  REPLACE(RIGHT(CONVERT(varchar(10), DATEADD(MONTH, number, GETDATE()), 103), 7), '/', '')
FROM    master.dbo.spt_values
WHERE   type = 'P'
        AND number BETWEEN 1 AND 240            -- change as needed

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
damixaAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.