Loop to create a Date Dimension

Alas, I continue to find loops and cursors a challenge so I thought I'd try it out with the basics... the syntax below can be used to create a date dimension on any server; it does not reference any database tables.

NOTE: Additional columns could be added by using numerous other SQL datepart (etc.) functions.

/******************************************************

* FOR DEVELOPERS who have the rights to build a table

*******************************************************

* For testing purposes, I like to create my own schema;

* the syntax below could be done as a temp table as well

* (scroll towards the bottom of this SQL).

*******************************************************/

/* SETUP: I like to create my own schema for testing...

*

* create schema epriddyTEST

*

* --drop table epriddyTEST.dimDate

* -- create table epriddyTEST.dimDate

* -- (FullDate datetime,

* -- Year int,

* -- DayOfWeek varchar(20))

* -- create clustered index FullDateIndex on epriddyTEST.dimDate(FullDate)

********************************************************/

--I like to make the calculations relative to today

declare @Today datetime

set @Today = convert(datetime,convert(char(10),getdate(),101))

---------------------------------------

--STEP1: set up the date range desired

---------------------------------------

declare @StartDate datetime

set @StartDate = dateadd(yy,-5,@Today) -- 5 years ago today

set @StartDate = '01/01/'+cast((datepart(yy,@StartDate)) as varchar) -- starting Jan. 1st

declare @EndDate datetime

set @EndDate = dateadd(yy,5,@Today) -- 5 years from now today

set @EndDate = '12/31/'+cast((datepart(yy,@EndDate)) as varchar) -- ending Dec. 31st

--USE QUERIES BELOW TO DOUBLE-CHECK Range Selected...

--Select Today = @Today, StartDate = @StartDate, EndDate = @EndDate

--select datediff(d,@StartDate,@EndDate) -- number of loops

---------------------------------------

--STEP2: Setup Loop piece

---------------------------------------

declare @count int; --NumberOfLoops

set @count = -1

while @count < (select datediff(d,@StartDate,@EndDate)) -- this is the total number of times it should loop

begin

set @count = @count + 1

insert into epriddyTEST.dimDate (FullDate, [Year],[DayOfWeek])

Select FullDate = @StartDate+@count

,[Year] = datepart(yy,(@StartDate+@count ))

,[DayOfWeek]=datename(weekday,(@StartDate+@count ))

end

Select * from epriddyTEST.dimDate

--All done!

/******************************************************

* FOR ANALYSTS and Others who may not have permissions

* to create permanent tables; here's a temp table option

*******************************************************/

--I like to make the calculations relative to today

declare @Today datetime

set @Today = convert(datetime,convert(char(10),getdate(),101))

---------------------------------------

--STEP1: set up the date range desired

---------------------------------------

declare @StartDate datetime

set @StartDate = dateadd(yy,-5,@Today) -- 5 years ago today

set @StartDate = '01/01/'+cast((datepart(yy,@StartDate)) as varchar) -- starting Jan. 1st

declare @EndDate datetime

set @EndDate = dateadd(yy,5,@Today) -- 5 years from now today

set @EndDate = '12/31/'+cast((datepart(yy,@EndDate)) as varchar) -- ending Dec. 31st

--USE QUERIES BELOW TO DOUBLE-CHECK Range Selected...

--Select Today = @Today, StartDate = @StartDate, EndDate = @EndDate

--select datediff(d,@StartDate,@EndDate) -- number of loops

---------------------------------------

--STEP2: Setup Loop piece

---------------------------------------

declare @count int; --NumberOfLoops

set @count = -1

IF (object_id ('tempdb.dbo.#dimDate') is not null)

BEGIN

DROP TABLE #dimDate

END

create table #dimDate

(FullDate datetime,

[Year] int,

[DayOfWeek] varchar(20),

MonthNum int,

MonthName varchar(20))

create clustered index FullDateIndex on #dimDate(FullDate) -- I always create a index if I have to use a temp table

while @count < (select datediff(d,@StartDate,@EndDate)) -- this is the total number of times it should loop

begin

set @count = @count + 1

insert into #dimDate (FullDate, [Year],[DayOfWeek],MonthNum, MonthName)

Select FullDate = @StartDate+@count

,[Year] = datepart(yy,(@StartDate+@count ))

,[DayOfWeek]=datename(weekday,(@StartDate+@count ))

,MonthNum = datepart(mm,(@StartDate+@count ))

,[MonthName] = DATENAME(month, (@StartDate+@count ))

end

Select * from #dimDate

--All Done!