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?
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
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 ConnectWise

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:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.