Working a project and needed generic date and time dimensions, wrote the following stored procedures, blogged here for future use…

 

The date one accepts 2 parameters a start date and an end date, it then creates a row for each day between these dates.

 

We had a bit of a discussion on the time one about whether it should go to the minute or second level so I’ve included a parameter called @grain which should be set to either ‘second’ or ‘minute’, so you can choose which level you need.

 

SEE COMMENTS FOR TIME DIMENSION SPROC…

 

USE[DanDev]

GO

/****** Object: StoredProcedure [dbo].[PopulateDimDate] Script Date: 05/19/2010 17:13:24 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATEprocedure [dbo].[PopulateDimDate] (@start asdatetime, @end asdatetime)

as

declare @dte asdatetime

— run the below statement to test

— exec PopulateDimDate ’01 Jan 2000′, ’31 Dec 2049′

— 150 years worth takes 22 seconds to create 54,787 rows

— either create or truncate the table

ifobject_id(‘DimDate’)isnull

begin

CREATE TABLE [dbo].[DimDate]

(

[DateKey] [int] NULL,

[CalDate] [datetime] NULL,

[TextDateShort] [nvarchar](6) NULL,

[TextDateLong] [nvarchar](11) NULL,

[CalWeek] [int] NULL,

[CalMonthNum] [int] NULL,

[CalQtr] [int] NULL,

[CalYear] [int] NULL,

[IsWeekend] [bit] NULL,

[DoW] [nvarchar](9) NULL,

[DoM] [int] NULL,

[CalMonthName] [nvarchar](9) NULL,

[IsFirstDayOfMonth] [bit] NULL,

[IsLastDayOfMonth] [bit] NULL,

[IsLeapYear] [bit] NULL

)

end

else

truncate table dbo.DimDate

— use while to create all rows with just date field populated to keep it quick as poss

set @dte = @start

while @dte <= @end

begin

insert into DimDate (CalDate) values (@dte)

set @dte = dateadd(dd,1,@dte)

end

— Now populate all additional fields

update DimDate

set DateKey = year(CalDate)*10000+month(CalDate)*100+day(CalDate),

TextDateShort = left(convert(varchar(16),CalDate,113),6),

TextDateLong = left(convert(varchar(16),CalDate,113),11),

CalWeek = datepart(week,CalDate),

CalMonthNum = month(CalDate),

CalQtr = (month(CalDate)/4)+1,

CalYear = year(CalDate),

IsWeekend = case when datename(dw,CalDate) in(‘Saturday’,‘Sunday’) then ‘TRUE’ else ‘FALSE’ end,

DoW = datename(dw,CalDate),

DoM = day(CalDate),

CalMonthName = datename(month,CalDate),

IsFirstDayOfMonth = case when day(CalDate) = 1 then ‘TRUE’ else ‘FALSE’ end,

IsLastDayOfMonth = case when day(dateadd(dd,1,CalDate)) = 1 then ‘TRUE’ else ‘FALSE’ end,

IsLeapYear = case when year(CalDate) % 4 = 0

and (year(CalDate) % 100 <> 0

or year(CalDate) % 400 = 0) then ‘TRUE’ else ‘FALSE’ end