CREATE
PROCEDURE [dbo].[usp_Create_dim_Date_Table]
(
@StartDate smalldatetime = ’20080101′,
@EndDate smalldatetime = ’20081231′
)
AS
BEGIN
SET NOCOUNT ON;
IF
EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[dbo].[dim_Date]‘) AND type in(N’U’))
DROP
TABLE [dbo].[dim_Date]
CREATE
TABLE dbo.dim_Date(
[MonthKey] [int] NOT NULL,
[MonthName] [nvarchar](15) NOT NULL,
[YearKey] [smallint] NOT NULL,
[YearName] [nvarchar](4) NOT NULL,
[MonthOfYearKey] [tinyint] NOT NULL,
[MonthOfYearName] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_dim_Date] PRIMARY KEY CLUSTERED
(
[MonthKey] ASC
)
)
set
language English – or any other
set
datefirst 1 – 7 for Sunday, 1 for Monday
set
dateformat dmy – set differently if necessary
declare
@CurrentDate smalldatetime
declare
@TimeID as int
set
@CurrentDate = @StartDate
set
@TimeID = 1
while
( @CurrentDate <= @EndDate )
begin
insert into [dbo].[dim_Date]
(
[MonthKey] ,
[MonthName] ,
[YearKey] ,
[YearName] ,
[MonthOfYearKey] ,
[MonthOfYearName]
)
values
(
– month
datepart(year, @CurrentDate ) * 100 + datepart(month, @CurrentDate ),
convert(varchar(4), datepart(year, @CurrentDate )) + ‘ ‘ +
‘M ‘ + convert(char(2), datepart(month, @CurrentDate )),
– year
datepart(year, @CurrentDate ),
convert(varchar(4), datepart(year, @CurrentDate )),
– month of year
datepart(month, @CurrentDate ),
datename(month, @CurrentDate )
)
set @TimeID = @TimeID + 1
set @CurrentDate = dateadd( month, 1, @CurrentDate )
end
END
Tweak some options (see comments).
