Autopopulate SQL field with mmyyyy dates for the next 20 years

Hi,
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:

022015
032015
032015
.....

...

122050

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

thanks,
damixaAsked:
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.

 
UnifiedISCommented:
DECLARE @I int
SET @I = 0

DECLARE @Dates Table (
      MonthYear char(6)
      )

WHILE @I < (20 * 12)
      BEGIN
            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)
                  CONTINUE
            ELSE
                  BREAK

      END

SELECT MonthYear
FROM @Dates
0
 
PortletPaulfreelancerCommented:
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 
select
        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

0
 
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
0

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:
Thanks
0
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.