Date Dimension Table
ZTZenoti Docs Team Updated April 10, 2026 3 min readGuide
Dimension table storing calendar date attributes including day, week, month, quarter, year breakdowns for both standard (USA/UK) and fiscal calendars. Pre-populated with a range of dates for time-based analysis in the data warehouse.
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| datewid | BIGINT IDENTITY | No | Surrogate warehouse key for the date dimension, auto-generated |
| datekey | INTEGER | Yes | Integer representation of the date (typically YYYYMMDD format) |
| date | TIMESTAMP | Yes | The actual calendar date |
| fulldateuk | CHAR(40) | Yes | Full date string in UK format (dd/MM/yyyy) |
| fulldateusa | CHAR(40) | Yes | Full date string in USA format (MM/dd/yyyy) |
| dayofmonth | VARCHAR(8) | Yes | Day number within the month (1-31) |
| daysuffix | VARCHAR(16) | Yes | Day ordinal suffix (e.g., 1st, 2nd, 3rd) |
| dayname | VARCHAR(36) | Yes | Name of the day of the week (e.g., Monday) |
| dayofweekusa | CHAR(4) | Yes | Day of week number (USA convention, Sunday = 1) |
| dayofweekuk | CHAR(4) | Yes | Day of week number (UK convention, Monday = 1) |
| dayofweekinmonth | VARCHAR(8) | Yes | Occurrence of this day within the month (e.g., 2nd Tuesday) |
| dayofweekinyear | VARCHAR(8) | Yes | Occurrence of this day within the year |
| dayofquarter | VARCHAR(12) | Yes | Day number within the quarter |
| dayofyear | VARCHAR(12) | Yes | Day number within the year (1-366) |
| weekofmonth | VARCHAR(4) | Yes | Week number within the month |
| weekofquarter | VARCHAR(8) | Yes | Week number within the quarter |
| weekofyear | VARCHAR(8) | Yes | Week number within the year (1-53) |
| month | VARCHAR(8) | Yes | Month number (1-12) |
| monthname | VARCHAR(36) | Yes | Name of the month (e.g., January) |
| monthofquarter | VARCHAR(8) | Yes | Month number within the quarter (1-3) |
| quarter | CHAR(4) | Yes | Quarter number (1-4) |
| quartername | VARCHAR(36) | Yes | Quarter display name (e.g., Q1, First Quarter) |
| year | CHAR(16) | Yes | Calendar year (e.g., 2024) |
| yearname | CHAR(28) | Yes | Display name for the year |
| monthyear | CHAR(40) | Yes | Combined month and year string (e.g., January 2024) |
| mmyyyy | CHAR(24) | Yes | Month-year in MM/YYYY format |
| firstdayofmonth | DATE | Yes | First day of the month for the given date |
| lastdayofmonth | DATE | Yes | Last day of the month for the given date |
| firstdayofquarter | DATE | Yes | First day of the quarter for the given date |
| lastdayofquarter | DATE | Yes | Last day of the quarter for the given date |
| firstdayofyear | DATE | Yes | First day of the year for the given date |
| lastdayofyear | DATE | Yes | Last day of the year for the given date |
| isholidayusa | BOOLEAN | Yes | Whether the date is a USA public holiday |
| isweekday | BOOLEAN | Yes | Whether the date is a weekday (Monday-Friday) |
| holidayusa | VARCHAR(200) | Yes | Name of the USA holiday (if applicable) |
| isholidayuk | BOOLEAN | Yes | Whether the date is a UK public holiday |
| holidayuk | VARCHAR(200) | Yes | Name of the UK holiday (if applicable) |
| fiscaldayofyear | VARCHAR(12) | Yes | Day number within the fiscal year |
| fiscalweekofyear | VARCHAR(12) | Yes | Week number within the fiscal year |
| fiscalmonth | VARCHAR(8) | Yes | Fiscal month number |
| fiscalquarter | CHAR(4) | Yes | Fiscal quarter number |
| fiscalquartername | VARCHAR(36) | Yes | Fiscal quarter display name |
| fiscalyear | CHAR(16) | Yes | Fiscal year |
| fiscalyearname | CHAR(28) | Yes | Fiscal year display name |
| fiscalmonthyear | CHAR(40) | Yes | Fiscal month and year combined string |
| fiscalmmyyyy | CHAR(24) | Yes | Fiscal month-year in MM/YYYY format |
| fiscalfirstdayofmonth | DATE | Yes | First day of the fiscal month |
| fiscallastdayofmonth | DATE | Yes | Last day of the fiscal month |
| fiscalfirstdayofquarter | DATE | Yes | First day of the fiscal quarter |
| fiscallastdayofquarter | DATE | Yes | Last day of the fiscal quarter |
| fiscalfirstdayofyear | DATE | Yes | First day of the fiscal year |
| fiscallastdayofyear | DATE | Yes | Last day of the fiscal year |
| void | BOOLEAN | Yes | Soft-delete flag; TRUE indicates the record is voided/inactive |
Note: All dimension tables also include standard ETL metadata columns (
podname,organizationid,groupid,etlLastUpdatedOn,etlLastUpdatedBy) which are omitted from this reference for brevity.
Primary Key
datewid
Foreign Keys
This table has no foreign key relationships to other analytics tables.
Was this article helpful?
Your feedback helps us improve our documentation.