ZenotiHelp

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.