Script for Dimension table for Time.
Here Date_ID is the primary key which can be used as the
foreign key in the fact tables.
CREATE TABLE [dbo].[DATE_DIM]
(
[DATE_ID] [bigint] IDENTITY(1, 1)
NOT NULL
,[SQL_DATE]
[datetime] NOT NULL
,[DAY]
[smallint] NOT NULL
,[DAY_OF_WEEK]
[smallint] NULL
,[WEEK]
[smallint] NOT NULL
,[MONTH]
[SMALLINT] NOT NULL
,[QUARTER]
[SMALLINT] NOT NULL
,[YEAR]
[SMALLINT] NOT NULL
,[DAY_OF_YEAR]
[SMALLINT] NOT NULL
,[DAY_TEXT]
[VARCHAR](50) NOT NULL
,[MONTH_TEXT]
[VARCHAR](50) NOT NULL
,[QUARTER_TEXT]
[VARCHAR](50) NOT NULL
)
declare @date datetime
declare
@enddate datetime
set @date = CONVERT(datetime,'01-01-2012')
set @enddate = CONVERT(datetime,'12-31-2025')
while @date <= @enddate
begin
insert into DATE_DIM
([SQL_DATE]
,[day]
,[DAY_OF_WEEK]
,[WEEK]
,[MONTH]
,[QUARTER]
,[YEAR]
,[DAY_OF_YEAR]
,[DAY_TEXT]
,[MONTH_TEXT]
,[QUARTER_TEXT]
)
select
@date as
sqldate
,datepart(D,@date) as [day]
,datepart(DW,@date) as [day_of_week]
,DATEPART(WK,@date) as [week]
,DATEPART (M,@date) as [month]
,DATEPART(Q,@date) as [quarter]
,DATEPART(DY,@date) as [day_of_year]
,DATEPART(Y,@date) as [year]
,DATENAME(DW,@date) as day_text
,DATENAME(M,@date) as month_text
,'Q' + CONVERT(CHAR(1), DATENAME(QQ, @DATE)) AS QUARTERTEXT
set @date = DATEADD(D,1,@date)
end
