CREATE TABLE [dbo].[DimDate]([DateKey] [int] NOT NULL,[FullDateAlternateKey] [date] NOT NULL,[DayNumberOfWeek] [tinyint] NOT NULL,[EnglishDayNameOfWeek] [nvarchar](10) NOT NULL,[DayNumberOfMonth] [tinyint] NOT NULL,[DayNumberOfYear] [smallint] NOT NULL,[WeekNumberOfYear] [tinyint] NOT NULL,[EnglishMonthName] [nvarchar](10) NOT NULL,[MonthNumberOfYear] [tinyint] NOT NULL,[CalendarQuarter] [tinyint] NOT NULL,[CalendarYear] [smallint] NOT NULL,[CalendarSemester] [tinyint] NOT NULL,[FiscalQuarter] [tinyint] NOT NULL,[FiscalYear] [smallint] NOT NULL,[FiscalSemester] [tinyint] NOT NULL,CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED([DateKey] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],CONSTRAINT [AK_DimDate_FullDateAlternateKey] UNIQUE NONCLUSTERED([FullDateAlternateKey] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]   GO   BEGIN TRAN   declare @startdate date = ‘2005-01-01‘,@enddate date = ‘2015-12-31‘Declare @datelist table (FullDate date)   while @startdate <= @enddateBeginInsert into @datelist (FullDate)Select @startdateSet @startdate = dateadd(dd,1,@startdate)endInsert into dbo.DimDate(DateKey,FullDateAlternateKey,DayNumberOfWeek,EnglishDayNameOfWeek,DayNumberOfMonth,DayNumberOfYear,WeekNumberOfYear,EnglishMonthName,MonthNumberOfYear,CalendarQuarter,CalendarYear,CalendarSemester,FiscalQuarter,FiscalYear,FiscalSemester)   select convert(int,convert(varchar,dl.FullDate,112)) as DateKey,dl.FullDate,datepart(dw,dl.FullDate) as DayNumberOfWeek,datename(weekday,dl.FullDate) as EnglishDayNameOfWeek,datepart(d,dl.FullDate) as DayNumberOfMonth,datepart(dy,dl.FullDate) as DayNumberOfYear,datepart(wk, dl.FUllDate) as WeekNumberOfYear,datename(MONTH,dl.FullDate) as EnglishMonthName,Month(dl.FullDate) as MonthNumberOfYear,datepart(qq, dl.FullDate) as CalendarQuarter,year(dl.FullDate) as CalendarYear,case datepart(qq, dl.FullDate)when 1 then 1when 2 then 1when 3 then 2when 4 then 2end as CalendarSemester,case datepart(qq, dl.FullDate)when 1 then 3when 2 then 4when 3 then 1when 4 then 2end as FiscalQuarter,case datepart(qq, dl.FullDate)when 1 then year(dl.FullDate)when 2 then year(dl.FullDate)when 3 then year(dl.FullDate) + 1when 4 then year(dl.FullDate) + 1end as FiscalYear,case datepart(qq, dl.FullDate)when 1 then 2when 2 then 2when 3 then 1when 4 then 1end as FiscalSemesterfrom @datelist dl ;   commit tran;