How to Build a Date Dimension Table

Almost every data warehouse has one, and almost every one is built slightly differently: a Date dimension. It looks like the simplest table in the whole model, just a list of days, but it's also the one every fact table joins to, so the design decisions made here get multiplied by every query that ever runs against the warehouse.

Why Bother, Instead of Just Using a Date Column?

You could join fact tables directly on a native date column and compute everything with functions at query time. Two reasons that's the wrong default:

Wrapping a date column in a function kills sargability. WHERE YEAR(OrderDate) = 2026 forces a scan instead of a seek, the same problem covered in Never Put Functions in WHERE Clauses. A Date dimension lets you filter on WHERE d.CalendarYear = 2026 against an indexed integer column instead.
Fiscal years, holidays, and "is a working day" aren't derivable from a date at all. SQL Server can tell you it's a Tuesday; it cannot tell you your company's fiscal Q3 starts in October, or that the 24th is a half-day. That's business logic, and it has to live somewhere, a maintained table is the right place.

Choosing the Grain and the Key

The grain is one row per calendar day. The key is where opinions diverge, and the Kimball convention has become the default for a reason:

Key choice	Example	Trade-off
Integer surrogate, `YYYYMMDD`	`20260315`	Smallest join column, human-readable in ad-hoc queries, naturally sortable, still an integer index
Native `date` as the key	`2026-03-15`	Simpler to reason about, but loses the option of a dedicated "unknown/not applicable" key like `-1` or `19000101`
Identity integer, unrelated to the date	`1`, `2`, `3`...	Meaningless without a lookup; almost never worth it for a dimension this small and this stable

Use the YYYYMMDD integer. It gives you a small, fast join key and, unlike an opaque identity value, you can read OrderDateKey = 20260315 directly without a lookup.

Which Columns to Include

Every calendar attribute a report might ever group or filter by should be a pre-computed column, not something recalculated at query time:

Calendar attributes: FullDate, DayOfWeek, DayName, DayOfMonth, DayOfYear, WeekOfYear, MonthNumber, MonthName, CalendarQuarter, CalendarYear.
Flags: IsWeekend, IsHoliday, IsLastDayOfMonth, boolean columns are cheap to store and make report filters trivial to write.
Fiscal attributes, if your fiscal year doesn't align with the calendar year: FiscalYear, FiscalQuarter, FiscalMonth, computed with a fixed month offset from the calendar columns.
Formatted labels for report axes: "Mar 2026", "2026-Q1", so report authors don't reformat dates in every visual.

Building It: Set-Based, Not Row-by-Row

Don't populate this with a WHILE loop. A cursor or WHILE loop inserting one row per day, then computing each column with a function call, is the exact same row-by-row anti-pattern covered in Why to Avoid the Slowly Changing Dimension Component in SSIS. A Date dimension for even fifty years is only about 18,000 rows, trivial for a set-based approach, and completely unnecessary to loop over.

Generate the date range with a numbers/tally sequence and compute every column in one INSERT ... SELECT:

CREATE TABLE dbo.DimDate
(
    DateKey            INT          NOT NULL PRIMARY KEY,  -- YYYYMMDD
    FullDate           DATE         NOT NULL,
    DayOfWeek          TINYINT      NOT NULL,
    DayName            VARCHAR(10)  NOT NULL,
    DayOfMonth         TINYINT      NOT NULL,
    DayOfYear          SMALLINT     NOT NULL,
    WeekOfYear         TINYINT      NOT NULL,
    MonthNumber        TINYINT      NOT NULL,
    MonthName          VARCHAR(10)  NOT NULL,
    CalendarQuarter    TINYINT      NOT NULL,
    CalendarYear       SMALLINT     NOT NULL,
    IsWeekend          BIT          NOT NULL,
    IsHoliday          BIT          NOT NULL DEFAULT (0),
    FiscalYear         SMALLINT     NOT NULL,
    FiscalQuarter      TINYINT      NOT NULL,
    FiscalMonth        TINYINT      NOT NULL,
    MonthYearLabel     VARCHAR(10)  NOT NULL
);
GO

DECLARE @StartDate DATE = '2015-01-01';
DECLARE @EndDate   DATE = '2035-12-31';
DECLARE @FiscalYearStartMonth INT = 4;  -- e.g. fiscal year starts in April

;WITH Tally AS
(
    SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate) + 1)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
),
Dates AS
(
    SELECT DATEADD(DAY, n, @StartDate) AS FullDate
    FROM Tally
)
INSERT INTO dbo.DimDate
SELECT
    CONVERT(INT, FORMAT(FullDate, 'yyyyMMdd'))                         AS DateKey,
    FullDate,
    DATEPART(WEEKDAY, FullDate)                                        AS DayOfWeek,
    DATENAME(WEEKDAY, FullDate)                                        AS DayName,
    DATEPART(DAY, FullDate)                                             AS DayOfMonth,
    DATEPART(DAYOFYEAR, FullDate)                                       AS DayOfYear,
    DATEPART(WEEK, FullDate)                                            AS WeekOfYear,
    MONTH(FullDate)                                                     AS MonthNumber,
    DATENAME(MONTH, FullDate)                                           AS MonthName,
    DATEPART(QUARTER, FullDate)                                         AS CalendarQuarter,
    YEAR(FullDate)                                                      AS CalendarYear,
    CASE WHEN DATEPART(WEEKDAY, FullDate) IN (1, 7) THEN 1 ELSE 0 END   AS IsWeekend,
    0                                                                   AS IsHoliday,
    YEAR(DATEADD(MONTH, 1 - @FiscalYearStartMonth, FullDate))           AS FiscalYear,
    DATEPART(QUARTER, DATEADD(MONTH, 1 - @FiscalYearStartMonth, FullDate)) AS FiscalQuarter,
    ((MONTH(FullDate) - @FiscalYearStartMonth + 12) % 12) + 1           AS FiscalMonth,
    FORMAT(FullDate, 'MMM yyyy')                                        AS MonthYearLabel
FROM Dates
ORDER BY FullDate;
GO

One statement, one transaction, the entire calendar range built in a single set-based pass, no loop, no per-row round trip.

Where It Fits in the Star Schema

Common Gotchas

Missing an "unknown" row. Fact rows with a genuinely unresolvable date (data quality issues, not-yet-shipped orders) need a key to point at, add a single row like DateKey = -1 or 19000101 with "Unknown" labels, rather than letting the FK go NULL or fail.
Holidays don't compute themselves. Fixed-date holidays are easy; floating ones (Easter, Thanksgiving, regional public holidays) need either a maintained lookup table or a one-time calculation script, and multi-country warehouses need one IsHoliday column per relevant region, not a single global flag.
Role-playing dimensions confuse people once, then never again. A single fact table often needs OrderDateKey, ShipDateKey, and DueDateKey, all joining the same DimDate table through separate foreign keys (and separate views or aliases in the semantic model), not three copies of the table.
Building the range too narrow. Extend a few years past your current planning horizon; regenerating and re-pointing every fact table's foreign keys later is far more disruptive than reserving rows for years you don't need yet.
Forgetting to index the columns reports actually filter on. CalendarYear, FiscalYear, and MonthYearLabel are common filter/group-by targets; a table this small barely needs it for performance, but it costs nothing to add.

The Bottom Line

A Date dimension is a few hundred lines of set-based T-SQL and a handful of design decisions made once: an integer YYYYMMDD key, every calendar and fiscal attribute pre-computed instead of derived at query time, and an explicit unknown-date row. Get those right up front and it's the one table in the warehouse you never have to touch again, every fact table just joins to it and gets years of business calendar logic for free.

← Back to Blog