SQL Query to Create a Date Dimension

DECLARE  @dtStartDate  DATETIME
DECLARE  @dtEndDate  DATETIME
DECLARE  @dtCurrDate  DATETIME
SELECT  @dtStartDate = '1/1/2010'
SELECT  @dtEndDate = '12/31/2010'
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 datetemp from @datedim