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