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
