Date Dimension Table Script

Script to create a Date Dimension table which is default to most of the data warehouse development

USE [xxxxx] --Database name

GO

DECLARE @dtStartDate DATETIME
DECLARE @dtEndDate DATETIME
DECLARE @dtCurrDate DATETIME

SELECT @dtStartDate = '1/1/2012'
SELECT @dtEndDate = '12/31/2012'

SELECT @dtCurrDate=NULL

SELECT @dtCurrDate = ISNULL (@dtCurrDate, @dtStartDate)
Declare @datedim TABLE
(
Datekey DATETIME
,DateWithDayName NVARCHAR(500)
,CurrYear INT
,CalYear NVARCHAR(100)
,FirstDayOfQtr DATETIME
,QTYYear NVARCHAR(250)
,FirstDayOfMonth DATETIME
,MonthYear NVARCHAR(500)
,DayOfTheYear INT
,DayOfTheYearname NVARCHAR(50)
,Day_of_the_quarter INT
,Day_of_the_quarter_name NVARCHAR(200)
,Day_of_month INT
,Day_of_month_name NVARCHAR(250)
,Month_of_year INT
,Month_of_year_name NVARCHAR(250)
,Month_of_quarter INT
,Month_of_quarter_name NVARCHAR(250)
,Quarter_in_year INT
,Quarter_in_year_name NVARCHAR(205)
,MMDDYYYY NVARCHAR(10)
,DDMMYYYY NVARCHAR(10)
,YYYYDDMM NVARCHAR(10)
,Semester_in_year int
,Semester_in_year_name NVARCHAR(15)
,Month_of_semester int
,Month_of_semester_name NVARCHAR(25)
,Day_of_the_semester int
,Day_of_the_semester_name NVARCHAR(30)
)

WHILE @dtCurrDate <= @dtEndDate
BEGIN
INSERT INTO @datedim
(datekey
,datewithdayname
,curryear
,calyear
,FirstDayOfQtr
,QTYYear
,FirstDayOfMonth
,MonthYear
,DayOfTheYear
,DayOfTheYearname
,day_of_the_quarter
,day_of_the_quarter_name
,day_of_month
,day_of_month_name
,Month_of_year
,Month_of_year_name
,Month_of_quarter
,Month_of_quarter_name
,quarter_in_year
,quarter_in_year_name
,MMDDYYYY
,DDMMYYYY
,YYYYDDMM
,semester_in_year
,semester_in_year_name
,month_of_semester
,month_of_semester_name
,day_of_the_semester
,day_of_the_semester_name
)
SELECT @dtCurrDate AS datekey
,DATENAME(DW, @dtCurrDate)+', '+DATENAME(month, @dtCurrDate)
+ ' ' +CONVERT(NVARCHAR (2) , DAY(@dtCurrDate)) + ' ' +Convert (NVARCHAR(4),YEAR(@dtCurrDate) ) as datewithdayname
,YEAR(@dtCurrDate) as curryear
, ' Calendar'+Convert (NVARCHAR(4),YEAR(@dtCurrDate) ) as calyear
,CASE WHEN DATEPART(qq, @dtCurrDate) IN (1,2,3) THEN Convert(DATETIME,'01/01/'+ CONVERT(varchar(4),YEAR(@dtCurrDate)))
WHEN DATEPART(qq, @dtCurrDate) IN (4,5,6) THEN Convert(DATETIME,'04/01/'+ CONVERT(varchar(4),YEAR(@dtCurrDate)))
WHEN DATEPART(qq, @dtCurrDate) IN (7,8,9) THEN Convert(DATETIME,'07/01/'+ CONVERT(varchar(4),YEAR(@dtCurrDate)))
WHEN DATEPART(qq, @dtCurrDate) IN (10,11,12) THEN Convert(DATETIME,'10/01/'+ CONVERT(varchar(4),YEAR(@dtCurrDate)))
END as FirstDayOfQtr
,'Quarter '+ convert (NVARCHAR,DATEPART(qq, @dtCurrDate))+','+ convert (NVARCHAR,YEAR(@dtCurrDate)) as QTYYear
,CONVERT(DATETIME,Convert(NVARCHAR(2),Month(@dtCurrDate))+'/01/'+Convert(NVARCHAR(4),Year(@dtCurrDate))) as FirstDayOfMonth
,DATENAME(month,@dtCurrDate)+Convert(NVARCHAR(4),Year(@dtCurrDate)) as MonthYear
,DATEPART(DAYOFYEAR,@dtCurrDate) as DayOfTheYear
,'Day '+Convert(NVARCHAR(3),DATEPART(DAYOFYEAR,@dtCurrDate)) as DayOfTheYearname
--day of the quarter
,CASE WHEN Month( @dtCurrDate) IN (1,2,3) THEN DATEDIFF(DD,'1/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
WHEN Month( @dtCurrDate) IN (4,5,6) THEN DATEDIFF(DD,'4/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
WHEN Month( @dtCurrDate) IN (7,8,9) THEN DATEDIFF(DD,'7/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
WHEN Month( @dtCurrDate) IN (10,11,12) THEN DATEDIFF(DD,'10/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
END as day_of_the_quarter
--day of the quarter name
,CASE WHEN Month( @dtCurrDate) IN (1,2,3) THEN 'Day '+Convert(NVARCHAR,DATEDIFF(DD,'1/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1)
WHEN Month( @dtCurrDate) IN (4,5,6) THEN 'Day '+Convert(NVARCHAR,DATEDIFF(DD,'4/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1)
WHEN Month( @dtCurrDate) IN (7,8,9) THEN 'Day '+Convert(NVARCHAR,DATEDIFF(DD,'7/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1)
WHEN Month( @dtCurrDate) IN (10,11,12) THEN 'Day '+Convert(NVARCHAR,DATEDIFF(DD,'10/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1)
END as day_of_the_quarter_name
,Day(@dtCurrDate) as day_of_month
,'Day '+CONVERT(NVARCHAR,Day(@dtCurrDate)) as day_of_month_name

,Month(@dtCurrDate) as Month_of_year
,'Month '+CONVERT(NVARCHAR,Month(@dtCurrDate)) as Month_of_year_name
,CASE WHEN Month( @dtCurrDate) IN (1,2,3) THEN MONTH(@dtCurrDate)
WHEN Month( @dtCurrDate) IN (4,5,6) THEN MONTH(@dtCurrDate)-4+1
WHEN Month( @dtCurrDate) IN (7,8,9) THEN MONTH(@dtCurrDate)-7+1
WHEN Month( @dtCurrDate) IN (10,11,12) THEN MONTH(@dtCurrDate)-10+1
END as month_of_quarter
,CASE WHEN Month( @dtCurrDate) IN (1,2,3) THEN 'Month '+CONVERT(NVARCHAR, MONTH(@dtCurrDate))
WHEN Month( @dtCurrDate) IN (4,5,6) THEN 'Month '+CONVERT(NVARCHAR, MONTH(@dtCurrDate)-4+1)
WHEN Month( @dtCurrDate) IN (7,8,9) THEN 'Month '+CONVERT(NVARCHAR, MONTH(@dtCurrDate)-7+1)
WHEN Month( @dtCurrDate) IN (10,11,12) THEN 'Month '+CONVERT(NVARCHAR, MONTH(@dtCurrDate)-10+1)
END as month_of_quarter_name

,DATEPART(qq, @dtCurrDate) as quarter_in_year
,'Quarter '+Convert(NVARCHAR,DATEPART(qq, @dtCurrDate)) as quarter_in_year_name
,CASE
WHEN Month(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,Month(@dtCurrDate))
else CONVERT(NVARCHAR,Month(@dtCurrDate))
end
+
CASE
WHEN day(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,day(@dtCurrDate))
else CONVERT(NVARCHAR,day(@dtCurrDate))
end
+
CASE
WHEN year(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,year(@dtCurrDate))
else CONVERT(NVARCHAR,year(@dtCurrDate))
end
as MMDDYYYY
,
CASE
WHEN day(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,day(@dtCurrDate))
else CONVERT(NVARCHAR,day(@dtCurrDate))
end
+
CASE
WHEN Month(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,Month(@dtCurrDate))
else CONVERT(NVARCHAR,Month(@dtCurrDate))
end
+
CASE
WHEN year(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,year(@dtCurrDate))
else CONVERT(NVARCHAR,year(@dtCurrDate))
end
as DDMMYYYY
,
CASE
WHEN year(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,year(@dtCurrDate))
else CONVERT(NVARCHAR,year(@dtCurrDate))
end
+
CASE
WHEN day(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,day(@dtCurrDate))
else CONVERT(NVARCHAR,day(@dtCurrDate))
end
+
CASE
WHEN Month(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,Month(@dtCurrDate))
else CONVERT(NVARCHAR,Month(@dtCurrDate))
end
as YYYYDDMM

, CASE WHEN Month(@dtCurrDate) BETWEEN 1 AND 6 THEN 1 ELSE 2 END AS semester_in_year
,'Semester '+ CASE WHEN Month(@dtCurrDate) BETWEEN 1 AND 6 THEN '1' ELSE '2' END AS semester_in_year
,CASE WHEN Month( @dtCurrDate) IN (1,2,3,4,5,6) THEN MONTH(@dtCurrDate)
WHEN Month( @dtCurrDate) IN (7,8,9,10,11,12) THEN MONTH(@dtCurrDate)-7+1
END as month_of_semester
,CASE WHEN Month( @dtCurrDate) IN (1,2,3,4,5,6) THEN 'Month ' + Cast(MONTH(@dtCurrDate) AS nvarchar)
WHEN Month( @dtCurrDate) IN (7,8,9,10,11,12) THEN 'Month ' + CAST(MONTH(@dtCurrDate)-7+1 AS nvarchar)
END as month_of_semester_name
--day of the semester
,CASE WHEN Month( @dtCurrDate) IN (1,2,3,4,5,6) THEN DATEDIFF(DD,'1/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
WHEN Month( @dtCurrDate) IN (7,8,9,10,11,12) THEN DATEDIFF(DD,'7/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
END as day_of_the_semester
,'Day '+CASE WHEN Month( @dtCurrDate) IN (1,2,3,4,5,6) THEN Cast(DATEDIFF(DD,'1/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1 as nvarchar)
WHEN Month( @dtCurrDate) IN (7,8,9,10,11,12) THEN Cast(DATEDIFF(DD,'7/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1 as nvarchar)
END as day_of_the_semester_name

SELECT @dtCurrDate = DATEADD(d, 1, @dtCurrDate)
END

SELECT * INTO DimDate FROM @datedim