Create your own Date Dimension table

As we all know Date dimension table is very important table for any Business. Here I am trying to show a simple method to create Date dimension.
I used MS SQL Server to create the table.

First create the table in database using the following code.

create table datetimetable(
row_wid int not null,
[date] datetime not null,
[yago_dt] datetime not null,
[mago_dt] datetime not null,
[day] text not null,
[day_num] int not null,
[week] int not null,
[mon] text not null,
[mon_short] text not null,
[mon_num] int not null,
[mon_year] text not null,
[qtr_num] int not null,
[qtr_year] text not null,
[year] int not null,
constraint pk_date primary key clustered (row_wid)
)

Then copy the following code and execute it.

declare @startdate datetime
declare @enddate datetime
set @startdate ='20110101'
set @enddate  = '20151231'
declare @loopdate datetime
set @loopdate  = @startdate
while @loopdate  <= @enddate
begin
insert into datetimetable values (
(year(@loopdate) * 10000) + (month(@loopdate) * 100) + DAY(@loopdate),
@loopdate  ,
dateadd(month,-12,@loopdate),
dateadd(month,-1,@loopdate),
datename(dw,@loopdate),
datepart(dw,@loopdate),
datepart(wk,@loopdate),
datename(month,@loopdate),
left(datename(month,@loopdate),3),
month(@loopdate),
cast(year(@loopdate ) as varchar(4))+ 'M' + cast(month(@loopdate) as varchar(2)),
case when month(@loopdate) in (1,2,3) then 1
when month(@loopdate) in (4,5,6) then 2
when month(@loopdate) in (7,8,9) then 3
when month(@loopdate) in (10,11,12) then 4
end,

case when month(@loopdate) in (1,2,3) then cast(@loopdate as varchar(4))+'Q1'
when month(@loopdate) in (4,5,6) then cast(@loopdate as varchar(4))+'Q2'
when month(@loopdate) in (7,8,9) then cast(@loopdate as varchar(4))+'Q3'
when month(@loopdate) in (10,11,12) then cast(@loopdate as varchar(4))+'Q4'
end,
year(@loopdate)
)
set @loopdate = dateadd(d,1,@loopdate)
end


We have created the table, now bring this table into RPD.