SQL Server Business Intelligence Development Studio 中的 Analysis Services 开发工具提供了一个自动生成日期维度的功能，能够很方便的生成带有层次结构的日期维度。但是若想扩展已有日期维度中的时间范围则无法通过开发工具完成。以下提供一个存储过程用于生成日期维度数据。
说明：由于语言环境以及所生成的日期维度结构可存在较大差异，因此本脚本仅供思路参考。在实际应用时应根据具体情况进行改动。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: onelittlefox
-- Create date: 2011-01-06
-- Description: Fill date dim for SSAS
-- =============================================
ALTER PROCEDURE [dbo].[FillDateDim]
-- Add the parameters for the stored procedure here
@startDate datetime,
@endDate datetime,
@operation int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
DECLARE @CurrentDate Datetime;
SET @CurrentDate = @startDate;
SET LANGUAGE english;
IF @operation = 1
BEGIN
WHILE @CurrentDate < @endDate
BEGIN
INSERT INTO dbo.Dim_Date
(
[PK_日期],
[日期_名称],
[年],
[年_名称],
[季度],
[季度_名称],
[月份],
[月份_名称],
[每年的某一日],
[每年的某一日_名称],
[每个季度的某一日],
[每个季度的某一日_名称],
[每月的某一日],
[每月的某一日_名称],
[每年的某一月],
[每年的某一月_名称],
[每个季度的某一月],
[每个季度的某一月_名称],
[每年的某一季度],
[每年的某一季度_名称]
)
VALUES
(
@CurrentDate,
DATENAME(DW, @CurrentDate)+ ', ' + DATENAME(MM, @CurrentDate) + ' '
+ right('00' + CONVERT(char(2), DATEPART(D, @CurrentDate)), 2) + ' ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)),
DATEADD(YYYY, DATEDIFF(YYYY, 0, @CurrentDate), 0),
'Calendar ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)),
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0),
'Quarter ' + CONVERT(char(1), DATEPART(QQ, @CurrentDate)) + ', ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)),
DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0),
DATENAME(MM, @CurrentDate) + ' ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)),
DATEPART(DY, @CurrentDate),
RTRIM('Day ' + CONVERT(char(3), DATEPART(DY, @CurrentDate))),
DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1,
RTRIM('Day ' + CONVERT(char(3),DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1)),
DATEDIFF(D, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), @CurrentDate) + 1,
RTRIM('Day ' + CONVERT(char(2),DATEDIFF(D, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), @CurrentDate) + 1)),
DATEPART(MM, @CurrentDate),
RTRIM('Month ' + CONVERT(char(2),DATEPART(MM, @CurrentDate))),
DATEDIFF(MM, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1,
RTRIM('Month ' + CONVERT(char(2), DATEDIFF(MM, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1)),
DATEPART(QQ, @CurrentDate),
RTRIM('Quarter ' + CONVERT(char(1), DATEPART(QQ, @CurrentDate)))
);
SET @CurrentDate = DATEADD(D, 1, @CurrentDate);
END
END
ELSE
BEGIN
CREATE TABLE #tmp(
[PK_日期] [Datetime] NOT NULL,
[日期_名称] [nvarchar](50) NULL,
[年] [Datetime] NULL,
[年_名称] [nvarchar](50) NULL,
[季度] [Datetime] NULL,
[季度_名称] [nvarchar](50) NULL,
[月份] [Datetime] NULL,
[月份_名称] [nvarchar](50) NULL,
[每年的某一日] [int] NULL,
[每年的某一日_名称] [nvarchar](50) NULL,
[每个季度的某一日] [int] NULL,
[每个季度的某一日_名称] [nvarchar](50) NULL,
[每月的某一日] [int] NULL,
[每月的某一日_名称] [nvarchar](50) NULL,
[每年的某一月] [int] NULL,
[每年的某一月_名称] [nvarchar](50) NULL,
[每个季度的某一月] [int] NULL,
[每个季度的某一月_名称] [nvarchar](50) NULL,
[每年的某一季度] [int] NULL,
[每年的某一季度_名称] [nvarchar](50) NULL
);
WHILE @CurrentDate < @endDate
BEGIN
INSERT INTO #tmp
(
[PK_日期],
[日期_名称],
[年],
[年_名称],
[季度],
[季度_名称],
[月份],
[月份_名称],
[每年的某一日],
[每年的某一日_名称],
[每个季度的某一日],
[每个季度的某一日_名称],
[每月的某一日],
[每月的某一日_名称],
[每年的某一月],
[每年的某一月_名称],
[每个季度的某一月],
[每个季度的某一月_名称],
[每年的某一季度],
[每年的某一季度_名称]
)
VALUES
(
@CurrentDate,
DATENAME(DW, @CurrentDate)+ ', ' + DATENAME(MM, @CurrentDate) + ' '
+ right('00' + CONVERT(char(2), DATEPART(D, @CurrentDate)), 2) + ' ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)),
DATEADD(YYYY, DATEDIFF(YYYY, 0, @CurrentDate), 0),
'Calendar ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)),
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0),
'Quarter ' + CONVERT(char(1), DATEPART(QQ, @CurrentDate)) + ', ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)),
DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0),
DATENAME(MM, @CurrentDate) + ' ' + CONVERT(char(4), DATEPART(YYYY, @CurrentDate)),
DATEPART(DY, @CurrentDate),
RTRIM('Day ' + CONVERT(char(3), DATEPART(DY, @CurrentDate))),
DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1,
RTRIM('Day ' + CONVERT(char(3),DATEDIFF(D, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1)),
DATEDIFF(D, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), @CurrentDate) + 1,
RTRIM('Day ' + CONVERT(char(2),DATEDIFF(D, DATEADD(MM, DATEDIFF(MM, 0, @CurrentDate), 0), @CurrentDate) + 1)),
DATEPART(MM, @CurrentDate),
RTRIM('Month ' + CONVERT(char(2),DATEPART(MM, @CurrentDate))),
DATEDIFF(MM, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1,
RTRIM('Month ' + CONVERT(char(2), DATEDIFF(MM, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0), @CurrentDate) + 1)),
DATEPART(QQ, @CurrentDate),
RTRIM('Quarter ' + CONVERT(char(1), DATEPART(QQ, @CurrentDate)))
);
SET @CurrentDate = DATEADD(D, 1, @CurrentDate);
END
SELECT * FROM #tmp;
DROP TABLE #tmp;
END
END