We help IT Professionals succeed at work.

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,
Comment
Watch Question

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
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Top Expert 2012
Commented:
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

Author

Commented:
Thanks