2

I have problems when I try to create my DimDate.

That's what I have tried:

CREATE TABLE [dbo].[DimDate] (
    /*[ID] [int] IDENTITY(1,1) NOT NULL--Use this line if you just want an  autoincrementing counter AND COMMENT BELOW LINE*/
    [ID] [VARCHAR](10) NOT NULL --TO MAKE THE ID THE YYYYMMDD FORMAT USE THIS LINE AND COMMENT ABOVE LINE.
    ,[Date] [DATETIME] NOT NULL
    ,[StandardDate] [VARCHAR](10) NULL
    ,[Day] [CHAR](2) NOT NULL
    ,[DaySuffix] [VARCHAR](4) NOT NULL
    ,[DayOfWeek] [VARCHAR](9) NOT NULL
    ,[DOWInMonth] [TINYINT] NOT NULL
    ,[DayOfYear] [INT] NOT NULL
    ,[WeekOfMonth] [TINYINT] NOT NULL
    ,[WeekOfYear] [TINYINT] NOT NULL
    ,[Month] [CHAR](2) NOT NULL
    ,[MonthName] [VARCHAR](9) NOT NULL
    ,[Quarter] [VARCHAR] NOT NULL
    ,[QuarterName] [VARCHAR](6) NOT NULL
    ,[Year] [CHAR](4) NOT NULL
    ,[MonthYear] [VARCHAR](10) NULL
    ,[MMYYY] [VARCHAR](6) NULL
    ,[FirstDayOfMonth] [DATE] NULL
    ,[LastDayOfMonth] [DATE] NULL
    ,[FirstDayOfQuarter] [DATE] NULL
    ,[LastDayOfQuarter] [DATE] NULL
    ,[FirstDayOfYear] [DATE] NULL
    ,[LastDayOfYear] [DATE] NULL
    ,[HolidayText] [VARCHAR](50) NULL CONSTRAINT [PKDimDate] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (
        PAD_INDEX = OFF
        ,STATISTICS_NORECOMPUTE = OFF
        ,IGNORE_DUP_KEY = OFF
        ,ALLOW_ROW_LOCKS = ON
        ,ALLOW_PAGE_LOCKS = ON
        ,FILLFACTOR = 90
        ) ON [PRIMARY]
    ) ON [PRIMARY]

/*******************************************************************************************************************************************************/
TRUNCATE TABLE [dbo].[DimDate]

/*IF YOU ARE USING THE YYYYMMDD format for the primary key then you need to comment out this line.
DBCC CHECKIDENT ([dbo].[DimDate], RESEED, 60000) --In case you need to add earlier dates later.*/
/*Table for counting DOW occurance in a month*/
DECLARE @tmpDOW TABLE (
    DOW INT
    ,Cntr INT
    )

INSERT INTO @tmpDOW (
    DOW
    ,Cntr
    )
VALUES (
    1
    ,0
    )

INSERT INTO @tmpDOW (
    DOW
    ,Cntr
    )
VALUES (
    2
    ,0
    )

INSERT INTO @tmpDOW (
    DOW
    ,Cntr
    )
VALUES (
    3
    ,0
    )

INSERT INTO @tmpDOW (
    DOW
    ,Cntr
    )
VALUES (
    4
    ,0
    )

INSERT INTO @tmpDOW (
    DOW
    ,Cntr
    )
VALUES (
    5
    ,0
    )

INSERT INTO @tmpDOW (
    DOW
    ,Cntr
    )
VALUES (
    6
    ,0
    )

INSERT INTO @tmpDOW (
    DOW
    ,Cntr
    )
VALUES (
    7
    ,0
    )

DECLARE @StartDate DATETIME
    ,@EndDate DATETIME
    ,@Date DATETIME
    ,@WDofMonth INT
    ,@CurrentMonth INT

SELECT @StartDate = '1/1/1995'
    /*Non inclusive. Stops on the day before this.*/
    ,@EndDate = '1/1/2025'
    ,@CurrentMonth = 1

SELECT @Date = @StartDate

/*******************************************************************************************************************************************************/
WHILE @Date < @EndDate
BEGIN
    IF DATEPART(MONTH, @Date) <> @CurrentMonth
    BEGIN
        SELECT @CurrentMonth = DATEPART(MONTH, @Date)

        UPDATE @tmpDOW
        SET Cntr = 0
    END

    UPDATE @tmpDOW
    SET Cntr = Cntr + 1
    WHERE DOW = DATEPART(DW, @DATE)

    SELECT @WDofMonth = Cntr
    FROM @tmpDOW
    WHERE DOW = DATEPART(DW, @DATE)

    INSERT INTO [dbo].[DimDate] (
        [ID]
        ,/*TO MAKE THE ID THE YYYYMMDD FORMAT UNCOMMENT THIS LINE... Comment for autoincrementing.*/
        [Date]
        ,[StandardDate]
        ,[Day]
        ,[DaySuffix]
        ,[DayOfWeek]
        ,[DOWInMonth]
        ,[DayOfYear]
        ,[WeekOfMonth]
        ,[WeekOfYear]
        ,[Month]
        ,[MonthName]
        ,[Quarter]
        ,[QuarterName]
        ,[Year]
        )
    SELECT CONVERT(VARCHAR, @Date, 112) AS [ID]
        ,/*TO MAKE THE ID THE YYYYMMDD FORMAT UNCOMMENT THIS LINE COMMENT FOR AUTOINCREMENT*/
        @Date AS [Date]
        ,RIGHT('0' + CONVERT(VARCHAR, DATEPART(MONTH, @DATE)), 2) + '/' + RIGHT('0' + CONVERT(VARCHAR, DATEPART(DAY, @DATE)), 2) + '/' + CONVERT(VARCHAR, DATEPART(YEAR, @Date)) AS [StandardDate]
        ,RIGHT('0' + CONVERT(VARCHAR, DATEPART(DAY, @DATE)), 2) AS [Day]
        ,CASE 
            WHEN DATEPART(DAY, @DATE) IN (
                    11
                    ,12
                    ,13
                    )
                THEN CAST(DATEPART(DAY, @DATE) AS VARCHAR) + 'th'
            WHEN RIGHT(DATEPART(DAY, @DATE), 1) = 1
                THEN CAST(DATEPART(DAY, @DATE) AS VARCHAR) + 'st'
            WHEN RIGHT(DATEPART(DAY, @DATE), 1) = 2
                THEN CAST(DATEPART(DAY, @DATE) AS VARCHAR) + 'nd'
            WHEN RIGHT(DATEPART(DAY, @DATE), 1) = 3
                THEN CAST(DATEPART(DAY, @DATE) AS VARCHAR) + 'rd'
            ELSE CAST(DATEPART(DAY, @DATE) AS VARCHAR) + 'th'
            END AS [DaySuffix]
        ,CASE DATEPART(DW, @DATE)
            WHEN 1
                THEN 'Sunday'
            WHEN 2
                THEN 'Monday'
            WHEN 3
                THEN 'Tuesday'
            WHEN 4
                THEN 'Wednesday'
            WHEN 5
                THEN 'Thursday'
            WHEN 6
                THEN 'Friday'
            WHEN 7
                THEN 'Saturday'
            END AS [DayOfWeek]
        ,@WDofMonth AS [DOWInMonth] /*Occurance of this day in this month. If Third Monday then 3 and DOW would be Monday.*/
        ,DATEPART(dy, @Date) AS [DayOfYear] /*Day of the year. 0 - 365/366*/
        ,DATEPART(ww, @Date) + 1 - DATEPART(ww, CAST(DATEPART(mm, @Date) AS VARCHAR) + '/1/' + CAST(DATEPART(yy, @Date) AS VARCHAR)) AS [WeekOfMonth]
        ,DATEPART(ww, @Date) AS [WeekOfYear] /*0-52/53*/
        ,RIGHT('0' + CONVERT(VARCHAR, DATEPART(MONTH, @DATE)), 2) AS [Month]
        ,DATENAME(MONTH, @DATE) AS [MonthName]
        ,DATEPART(qq, @DATE) AS [Quarter] /*Calendar quarter*/
        ,CASE DATEPART(qq, @DATE)
            WHEN 1
                THEN 'First'
            WHEN 2
                THEN 'Second'
            WHEN 3
                THEN 'Third'
            WHEN 4
                THEN 'Fourth'
            END AS [QuarterName]
        ,DATEPART(YEAR, @Date) AS [Year]

    SELECT @Date = DATEADD(dd, 1, @Date)
END

/*******************************************************************************************************************************************************/
/*Set first and last days of the months*/
UPDATE [dbo].[DimDate]
SET FirstDayOfMonth = minmax.StartDate
    ,LastDayOfMonth = minmax.EndDate
FROM [dbo].[DimDate] t
    ,(
        SELECT [Month]
            ,[Quarter]
            ,[Year]
            ,MIN([Date]) AS StartDate
            ,MAX([Date]) AS EndDate
        FROM [dbo].[DimDate]
        GROUP BY [Month]
            ,[Quarter]
            ,[Year]
        ) minmax
WHERE t.[Month] = minmax.[Month]
    AND t.[Quarter] = minmax.[Quarter]
    AND t.[Year] = minmax.[Year]

/*Set first and last days of the quarters*/
UPDATE [dbo].[DimDate]
SET FirstDayOfQuarter = minmax.StartDate
    ,LastDayOfQuarter = minmax.EndDate
FROM [dbo].[DimDate] t
    ,(
        SELECT [Quarter]
            ,[Year]
            ,min([Date]) AS StartDate
            ,max([Date]) AS EndDate
        FROM [dbo].[DimDate]
        GROUP BY [Quarter]
            ,[Year]
        ) minmax
WHERE t.[Quarter] = minmax.[Quarter]
    AND t.[Year] = minmax.[Year]

/*Set first and last days of the year*/
UPDATE [dbo].[DimDate]
SET FirstDayOfYear = minmax.StartDate
    ,LastDayOfYear = minmax.EndDate
FROM [dbo].[DimDate] t
    ,(
        SELECT [Year]
            ,min([Date]) AS StartDate
            ,max([Date]) AS EndDate
        FROM [dbo].[DimDate]
        GROUP BY [Year]
        ) minmax
WHERE t.[Year] = minmax.[Year]

/*Set YearMonth*/
UPDATE [dbo].[DimDate]
SET MonthYear = CASE [Month]
        WHEN 1
            THEN 'Jan'
        WHEN 2
            THEN 'Feb'
        WHEN 3
            THEN 'Mar'
        WHEN 4
            THEN 'Apr'
        WHEN 5
            THEN 'May'
        WHEN 6
            THEN 'Jun'
        WHEN 7
            THEN 'Jul'
        WHEN 8
            THEN 'Aug'
        WHEN 9
            THEN 'Sep'
        WHEN 10
            THEN 'Oct'
        WHEN 11
            THEN 'Nov'
        WHEN 12
            THEN 'Dec'
        END + '-' + CONVERT(VARCHAR, [Year])

/*Set MMYYY*/
UPDATE [dbo].[DimDate]
SET MMYYY = RIGHT('0' + CONVERT(VARCHAR, [Month]), 2) + CONVERT(VARCHAR, [Year])

/*******************************************************************************************************************************************************/
/*Add HOLIDAYS*/
/*THANKSGIVING - Fourth THURSDAY in November*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Thanksgiving Day'
WHERE [Month] = 11
    AND [DAYOFWEEK] = 'Thursday'
    AND [DOWInMonth] = 4
GO

/*CHRISTMAS*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Christmas Day'
WHERE [Month] = 12
    AND [Day] = 25
GO

/*4th of July*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Independance Day'
WHERE [Month] = 7
    AND [Day] = 4
GO

/*New Years Day*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'New Year''s Day'
WHERE [Month] = 1
    AND [Day] = 1
GO

/*Memorial Day - Last Monday in May*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Memorial Day'
FROM [dbo].[DimDate]
WHERE ID IN (
        SELECT MAX([ID])
        FROM [dbo].[DimDate]
        WHERE [MonthName] = 'May'
            AND [DayOfWeek] = 'Monday'
        GROUP BY [Year]
            ,[Month]
        )
GO

/*Labor Day - First Monday in September*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Labor Day'
FROM [dbo].[DimDate]
WHERE ID IN (
        SELECT MIN([ID])
        FROM [dbo].[DimDate]
        WHERE [MonthName] = 'September'
            AND [DayOfWeek] = 'Monday'
        GROUP BY [Year]
            ,[Month]
        )
GO

/*Valentine's Day*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Valentine''s Day'
WHERE [Month] = 2
    AND [Day] = 14
GO

/*Saint Patrick's Day*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Saint Patrick''s Day'
WHERE [Month] = 3
    AND [Day] = 17
GO

/*Martin Luthor King Day - Third Monday in January starting in 1983*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Martin Luthor King Jr Day'
WHERE [Month] = 1
    AND [Dayofweek] = 'Monday'
    AND [Year] >= 1983
    AND [DOWInMonth] = 3
GO

/*President's Day - Third Monday in February*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'President''s Day'
WHERE [Month] = 2
    AND [Dayofweek] = 'Monday'
    AND [DOWInMonth] = 3
GO

/*Mother's Day - Second Sunday of May*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Mother''s Day'
WHERE [Month] = 5
    AND [Dayofweek] = 'Sunday'
    AND [DOWInMonth] = 2
GO

/*Father's Day - Third Sunday of June*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Father''s Day'
WHERE [Month] = 6
    AND [Dayofweek] = 'Sunday'
    AND [DOWInMonth] = 3
GO

/*Halloween 10/31*/
UPDATE [dbo].[DimDate]
SET HolidayText = 'Halloween'
WHERE [Month] = 10
    AND [Day] = 31
GO

/*Election Day - The first Tuesday after the first Monday in November*/
BEGIN
    BEGIN TRY
        DROP TABLE #tmpHoliday
    END TRY

    BEGIN CATCH
        /*Do Nothing*/
    END CATCH

    CREATE TABLE #tmpHoliday (
        ID INT IDENTITY(1, 1)
        ,DateID INT
        ,Week TINYINT
        ,YEAR CHAR(4)
        ,DAY CHAR(2)
        )

    INSERT INTO #tmpHoliday (
        DateID
        ,[Year]
        ,[Day]
        )
    SELECT [ID]
        ,[Year]
        ,[Day]
    FROM [dbo].[DimDate]
    WHERE [Month] = 11
        AND [Dayofweek] = 'Monday'
    ORDER BY YEAR
        ,DAY

    DECLARE @CNTR INT
        ,@POS INT
        ,@STARTYEAR INT
        ,@ENDYEAR INT
        ,@CURRENTYEAR INT
        ,@MINDAY INT

    SELECT @CURRENTYEAR = MIN([Year])
        ,@STARTYEAR = MIN([Year])
        ,@ENDYEAR = MAX([Year])
    FROM #tmpHoliday

    WHILE @CURRENTYEAR <= @ENDYEAR
    BEGIN
        SELECT @CNTR = COUNT([Year])
        FROM #tmpHoliday
        WHERE [Year] = @CURRENTYEAR

        SET @POS = 1

        WHILE @POS <= @CNTR
        BEGIN
            SELECT @MINDAY = MIN(DAY)
            FROM #tmpHoliday
            WHERE [Year] = @CURRENTYEAR
                AND [Week] IS NULL

            UPDATE #tmpHoliday
            SET [Week] = @POS
            WHERE [Year] = @CURRENTYEAR
                AND [Day] = @MINDAY

            SELECT @POS = @POS + 1
        END

        SELECT @CURRENTYEAR = @CURRENTYEAR + 1
    END

    UPDATE DT
    SET HolidayText = 'Election Day'
    FROM [dbo].[DimDate] DT
    INNER JOIN #tmpHoliday HL ON (HL.DateID + 1) = DT.ID
    WHERE [Week] = 1

    DROP TABLE #tmpHoliday
END
GO

/*******************************************************************************************************************************************************/
CREATE UNIQUE NONCLUSTERED INDEX [IDXDimDateDate] ON [dbo].[DimDate] ([Date] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateDay] ON [dbo].[DimDate] ([Day] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateDayOfWeek] ON [dbo].[DimDate] ([DayOfWeek] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateDOWInMonth] ON [dbo].[DimDate] ([DOWInMonth] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateDayOfYear] ON [dbo].[DimDate] ([DayOfYear] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateWeekOfYear] ON [dbo].[DimDate] ([WeekOfYear] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateWeekOfMonth] ON [dbo].[DimDate] ([WeekOfMonth] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateMonth] ON [dbo].[DimDate] ([Month] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateMonthName] ON [dbo].[DimDate] ([MonthName] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateQuarter] ON [dbo].[DimDate] ([Quarter] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateQuarterName] ON [dbo].[DimDate] ([QuarterName] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDXDimDateYear] ON [dbo].[DimDate] ([Year] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_dim_Time_HolidayText] ON [dbo].[DimDate] ([HolidayText] ASC)
    WITH (
            PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = OFF
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF
            ,ONLINE = OFF
            ,ALLOW_ROW_LOCKS = ON
            ,ALLOW_PAGE_LOCKS = ON
            ,FILLFACTOR = 90
            ) ON [PRIMARY]

SELECT *
FROM [dbo].[DimDate]
ORDER BY ID

**The thing is, the query does not create all the range from 1995 to 2025. **

7
  • What are the dates that are missing?
    – datagod
    Commented Aug 28, 2015 at 15:41
  • So, what can you create? Does it cut off at an unexpected point such as the middle of month or a year? Are there gaps? etc.?
    – RLF
    Commented Aug 28, 2015 at 15:41
  • You're missing 1 day? Try changing WHILE @Date < @EndDate to WHILE @Date <= @EndDate. If it's something else, then please be much more specific about what you mean by "not...all the range" so we don't have to guess. Commented Aug 28, 2015 at 15:51
  • There are some gaps, for example two months between 2014 and 2015. I don't really know what It's happening
    – Miguel
    Commented Aug 28, 2015 at 16:41
  • 4
    What is the exact set of dates missing? What exact query are you using to determine those dates are missing? Why is ID varchar(10) instead of char(8)? What version of SQL Server are you using? Why is [Date] DATETIME and not SMALLDATETIME or DATE? Why are you specifying varchar without length? Where is Easter? Commented Aug 28, 2015 at 17:50

3 Answers 3

12

Assuming SQL Server 2008 or better (please always include version information with your questions).

I've recently written a tip on building Calendar tables, here:

But in the spirit of providing a more concise answer here...

I'll let you deal with holidays, but here's a much more efficient, set-based way to generate your 30 years of date dimensions. A lot of your CASE expressions, aggregates, updates and looping are simply not necessary, because they were constructed with a mindset of "handle each individual date separately." In databases you want to think in sets as much as possible. It's usually more efficient and almost always streamlines the code.

DECLARE @StartDate DATE = '19950101', @NumberOfYears INT = 30;

SET DATEFIRST 7;
SET DATEFORMAT mdy;
SET LANGUAGE US_ENGLISH;

DECLARE @EndDate DATE = DATEADD(DAY,-1,DATEADD(YEAR,@NumberOfYears,@StartDate));

CREATE TABLE #dim
(
  dt DATE PRIMARY KEY, 
  d AS DATEPART(DAY, dt),
  y AS DATEPART(YEAR, dt),
  q AS DATEPART(QUARTER, dt),
  m AS DATEPART(MONTH, dt),
  fm AS DATEADD(MONTH, DATEDIFF(MONTH, 0, dt), 0), -- first of month
  w AS DATEPART(WEEK, dt), -- maybe you want ISO_WEEK?
  wd AS DATEPART(WEEKDAY, dt),
  mn AS DATENAME(MONTH, dt),
  s101 AS CONVERT(CHAR(10), dt, 101), -- mm/dd/yyyy
  s103 AS CONVERT(CHAR(10), dt, 103), -- dd/mm/yyyy
  s112 AS CONVERT(CHAR(8),  dt, 112)  -- yyyymmdd
);

INSERT #dim(dt) SELECT d FROM
(
  SELECT d = DATEADD(DAY, rn-1, @StartDate)
  FROM 
  (
    SELECT TOP (DATEDIFF(DAY,@StartDate,@EndDate)+1) 
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    -- on my system this would support > 5 million days
    ORDER BY s1.[object_id]
  ) AS x
) AS y;

...just to break up the code blocks...

--INSERT dbo.DimDate(...)
SELECT
  ID = s112,
  [Date] = dt,
  StandardDate = s101,
  [Day] = LEFT(s103, 2),
  DaySuffix = CASE WHEN d / 10 = 1 THEN 'th' ELSE CASE RIGHT(d, 1) 
    WHEN '1' THEN 'st' 
    WHEN '2' THEN 'nd' 
    WHEN '3' THEN 'rd' 
    ELSE 'th' END END,
  [DayOfWeek]  = DATENAME(WEEKDAY, dt),
  [DOWInMonth] = ROW_NUMBER() OVER (PARTITION BY fm, wd ORDER BY dt),
  [DayOfYear]  = DATEPART(DAYOFYEAR, dt),
  WeekOfMonth  = DENSE_RANK() OVER (PARTITION BY y, m ORDER BY w),
  WeekOfYear   = w,
  [Month] = LEFT(s101, 2),
  [MonthName] = mn,
  [Quarter] = q,
  QuarterName = CASE q WHEN 1 THEN 'First'
    WHEN 2 THEN 'Second' WHEN 3 THEN 'Third'
    WHEN 4 THEN 'Fourth' END, 
  [Year] = y,
  MMYYYY = LEFT(s101, 2) + LEFT(s112, 4),
  MonthYear = LEFT(mn, 3) + LEFT(s112, 4),
  FirstDayOfMonth   = fm,
  LastDayOfMonth    = MAX(dt) OVER (PARTITION BY y, m),
  FirstDayOfQuarter = MIN(dt) OVER (PARTITION BY y, q),
  LastDayOfQuarter  = MAX(dt) OVER (PARTITION BY y, q),
  FirstDayOfYear    = MIN(dt) OVER (PARTITION BY y),
  LastDayOfYear     = MAX(dt) OVER (PARTITION BY y)
FROM #dim AS d
ORDER BY [Date]
OPTION (MAXDOP 1);

GO
DROP TABLE #dim;

For more on generating sets without loops (and why), see these posts:

As an aside, I think your "StandardDate" column is a bit of a misnomer. Outside of the USA, mm/dd/yyyy is far from standard; in fact, it is downright confusing. There are a few other aspects of your code and table design that could use attention (including your choice of DATETIME instead of SMALLDATETIME or DATE, VARCHAR(10) instead of CHAR(8), using VARCHAR without length, lazy shorthand like ww and mm, etc.); I'd recommend reading up on these posts:

There is a much more thorough checklist here, if you get bored:

1

You can use a range of integers to generate a range of dates using the DateDiff and DateAdd functions. Let's pretend you have a TVF that generates a range of integers already:

select [Date] = DateAdd(day, n, @date1)
from dbo.RangeSmallInt(0, Abs(DateDiff(day, @date1, @date2)))

Wrapping it up in a TVF:

-- Generate a range of up to 65,536 contiguous DATES
create function dbo.RangeDate (   
    @d1 date = null
  , @d2 date = null
)   
returns table
with schemabinding
as
return (
    select DateAdd(Day, N, case when @d1 <= @d2 then @d1 else @d2 end) as d
    from dbo.RangeSmallInt(case when @d1 is not null and @d2 is not null then 0 end, Abs(DateDiff(Day, @d1, @d2)))
);

Using RangeDate(@d1, @d2) to build a date dimension:

select Convert(varchar(10), Dates.d, 112) as [Id]
     , Year(Dates.d) as Year
     , Month(Dates.d) as Month
     , Day(Dates.d) as Day
     .....
from dbo.RangeDate('1970-01-01', '2099-12-31') Dates

Why use this style over what you already have?

  • Readability
  • Maintainability
  • Speed

Breaking the problem down into smaller components like this makes the problem much easier to reason about. We can worry about a single problem at a time: generate a sequence of numbers, convert a sequence of numbers to a sequence of dates, project the values we want in the date dimension.

In addition, we now have two very nice helper utilities that we can reuse for all kinds of problems.


My preferred method for generating numbers is this (there are tons of other methods):

-- Generate a range of up to 65,536 contiguous bigints
create function dbo.RangeSmallInt (
    @n1 bigint = null
  , @n2 bigint = null
)
returns table
with schemabinding
as
return (
    with Numbers(N) as (
        select N from (values -- build a table of 256 constants
            (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 16
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 32
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 48
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 64
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 80
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 96
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 112
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 128
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 144
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 160
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 176
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 192
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 208
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 224
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 240
          , (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1), (1) -- 256
        ) V (N)
    )    
    select top ( -- bail early if we've generated the appropriate number of rows
               case
                   when @n1 is not null and @n2 is not null then Abs(@n1 - @n2) + 1
                   else 0
               end
           )
           -- start counting from the lower of the two inputs
           Row_Number() over(order by (select null)) + case when @n1 <= @n2 then @n1 else @n2 end - 1 as n
    from Numbers a
    cross join Numbers b
    where Abs(@n1 - @n2) + 1 < 65537 -- make our intent clear to the optimizer: we'll NEVER generate more than 65536 rows (256 * 256)
);
1
  • The exact set of date are between '20140831' and '20141001'.
    – Miguel
    Commented Aug 31, 2015 at 13:41
0

I finally did it, just updating AdventureworksDW2008r2 DimDate, after that I have created calculation members. Just copy DimDate from AdventureworksDW2008r2 to your model, after that you can follow this querys in order to update the DimDate

Just following this query http://arcanecode.com/2013/07/22/updating-adventureworksdw2008r2-for-today/

2
  • Please give a more detailed answer as to how this answered your question. As it is written I don't think it will be very useful for future users.
    – Erik
    Commented Oct 21, 2015 at 21:22
  • @Erik It's just that, use the DimDate from AdventureWorksDW2008r2 and just use the queries located in that website. if you want I can copy it here
    – Miguel
    Commented Oct 22, 2015 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.