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 assqldate
,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
