We help IT Professionals succeed at work.

# Autopopulate SQL field with mmyyyy dates for the next 20 years

on
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

## View Solution Only

Commented:
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
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)
;
``````
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

Commented:
Thanks