SQL Server script to populate a table of sequential dates in multiple formats

I was writing lots of TSQL today for something I was working on, which in itself is a rather rare occasion since I do so much more infrastructure and architecture stuff nowadays. I needed to write a script to populate a table with a series of rows containing sequential dates. I had to create a table which had 2 columns in it, one was [date] datatype and the other needed to be a 6 character string representing the year and the month as numbers concatenated together, for example 15th March 2012 would become 201215.

Then this table needed several rows covering every day over a number of years. Here’s the code just in case anyone fancies reusing it.

create table #dates
(
CustomMonth char(6),
lookupdate date
)

declare @date_increment date
--change your start date here if you fancy
set @date_increment = '2005-01-01'

--change your end date here if you fancy
while @date_increment < '2012-12-31'
begin

set @date_increment = DATEADD(day,1,@date_increment)

insert #dates (CustomMonth, lookupdate)
select convert(char(4),(datepart(year,@date_increment)))
	+ RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, @date_increment)), 2),
@date_increment

end

--check it looks ok
select * from #dates

By Graham Kent

Code Club teacher, IT Director, currently living in London, misses being in Stockholm