Use dbname
GO
-- Create the time dimension table
CREATE TABLE [dbo].[Dim_Time](
[TimeKey] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Date] [varchar](8) NOT NULL,
[Year] [smallint] NOT NULL,
[NameOfMonth] [varchar](9) NOT NULL,
[Month] [varchar](2) NOT NULL,
[DayOfWeek] [varchar](9) NOT NULL,
[DayOfMonth] [smallint] NOT NULL,
[WeekOfYear] [smallint] NOT NULL,
)
GO
DECLARE @startdate VARCHAR(10)
DECLARE @enddate VARCHAR(10)
-- Specify date range for which you want to create time dimension
SET @startdate='2008-01-01';
SET @enddate='2015-12-31';
-- Populate the time dimension
WITH mycte AS
(
SELECT CAST(@startdate AS DATETIME) DateValue
UNION ALL
SELECT DateValue + 1 FROM mycte WHERE DateValue + 1 < @enddate
)
INSERT INTO Dim_Time([Date],[Year],[NameOfMonth],[Month],[DayOfWeek],[DayOfMonth],[WeekOfYear])
SELECT CONVERT (VARCHAR,datevalue,112)
,DATEPART(YY, DateValue)
,DATENAME(MM, DateValue)
,DATEPART(MM, DateValue)
,DATENAME(DW, DateValue)
,DATEPART(DD, DateValue)
,DATEPART(WK, DateValue)
from mycte OPTION (MAXRECURSION 0)
