How to Create a Date Dimension in SQL: Step by Step

A date dimension is the backbone of any star schema: it lets you analyse metrics by year, quarter, month, or day of the week without rewriting date logic in every report. By building it once and reusing it, you keep your numbers consistent across the whole data warehouse. Let's create a date dimension in SQL, step by step, and link it to a fact table.

Prerequisites

• Access to a SQL Server database (the example adapts to PostgreSQL with minor changes).
• Permissions to create tables and insert data.
• Basic understanding of a star schema: a fact table linked to dimension tables.

Step 1: Design the dimension columns

Before writing any SQL, decide which columns the dimension will have. The DateKey is an integer in YYYYMMDD format (for example, 20260705 for 5 July 2026). This kind of key — known as a smart key — is compact, sortable, and readable, and it makes joins with the fact table faster than comparing date values. Next, add the attributes people use to filter and group: year, quarter, month, month name, day, and a weekend flag. The rule of thumb is simple: if the business filters by an attribute, it deserves a column.

Step 2: Create the DimDate table

Create the table with DateKey as the primary key. Using small types such as tinyint and smallint keeps the dimension lightweight and fast to query. An integer key also lets you reserve special rows — for example, -1 for an unknown date — without breaking your joins.

CREATE TABLE DimDate (
    DateKey       int          NOT NULL PRIMARY KEY,
    FullDate      date         NOT NULL,
    YearNumber    smallint     NOT NULL,
    QuarterNumber tinyint      NOT NULL,
    MonthNumber   tinyint      NOT NULL,
    MonthName     varchar(20)  NOT NULL,
    DayNumber     tinyint      NOT NULL,
    DayName       varchar(20)  NOT NULL,
    IsWeekend     bit          NOT NULL
);

Step 3: Generate the dates with a recursive CTE

Now we fill a date range — here, from 2015 to 2030 — using a recursive CTE. The first row (the anchor) sets the start date; the recursive member adds one day at a time until it reaches the end. Each row derives its attributes from the date.

WITH Dates AS (
    SELECT CAST('2015-01-01' AS date) AS d
    UNION ALL
    SELECT DATEADD(DAY, 1, d) FROM Dates WHERE d < '2030-12-31'
)
INSERT INTO DimDate (DateKey, FullDate, YearNumber, QuarterNumber, MonthNumber, MonthName, DayNumber, DayName, IsWeekend)
SELECT
    CONVERT(int, FORMAT(d, 'yyyyMMdd')),
    d,
    YEAR(d),
    DATEPART(QUARTER, d),
    MONTH(d),
    DATENAME(MONTH, d),
    DAY(d),
    DATENAME(WEEKDAY, d),
    CASE WHEN (DATEDIFF(DAY, 0, d) % 7) IN (5, 6) THEN 1 ELSE 0 END
FROM Dates
OPTION (MAXRECURSION 0);

Note two details. The weekend CASE uses DATEDIFF(DAY, 0, d) % 7, a formula that is independent of the server language (5 is Saturday and 6 is Sunday). And OPTION (MAXRECURSION 0) removes the CTE's 100-iteration limit, so you can generate thousands of days at once.

The MonthName and DayName columns inherit the language of the SQL Server session. If you need names always in a specific language, set the session language or fill those texts manually.

Step 4: Link it to the fact table

The fact table should store the same integer DateKey, not the full date. That keeps the join fast and makes the relationship explicit through a foreign key.

ALTER TABLE FactSales
ADD CONSTRAINT FK_FactSales_DimDate
FOREIGN KEY (DateKey) REFERENCES DimDate (DateKey);

If you plan to use the model in Power BI, import DimDate, create the relationship on DateKey, and use Mark as a Date Table, pointing to FullDate. Only then do Time Intelligence functions, such as TOTALYTD, work correctly.

Verify the result

Confirm the dimension is populated and try an aggregation by period:

SELECT COUNT(*) AS TotalDays, MIN(FullDate) AS FirstDate, MAX(FullDate) AS LastDate
FROM DimDate;

SELECT d.YearNumber, d.QuarterNumber, SUM(f.Amount) AS Sales
FROM FactSales f
JOIN DimDate d ON f.DateKey = d.DateKey
GROUP BY d.YearNumber, d.QuarterNumber
ORDER BY d.YearNumber, d.QuarterNumber;

The first query should return about 5,844 days (from 2015 to 2030). The second shows sales by year and quarter — a sign that the dimension is correctly linked to the fact table.

Conclusion

With just a few lines of SQL, you now have a date dimension you can reuse across the whole warehouse. From here, you can enrich it with fiscal year, ISO week number, holidays, or relative-date columns such as "current month" or "previous year". Which attribute do your reports miss most — perhaps a per-country holidays column?