DIMDATE¶
This dimension table stores information about all dates (date keys) used.
Columns¶
| Column | Data type | Description |
|---|---|---|
APDATE |
DATE | The accounting period date. |
CALENDARDATE |
DATE | The date in a standard calendar format. |
DATEKEY |
NUMBER | The primary key referencing the date. |
DAYNAME |
VARCHAR | The name of the day of the week. |
DAYOFMONTH |
NUMBER | The day of the month as a number. |
DAYOFQTR |
NUMBER | The day of the quarter as a number. |
DAYOFWEEK |
NUMBER | The day of the week as a number from 1 to 7. Number 1 corresponds to Sunday and 7 to Saturday. |
DAYOFYEAR |
NUMBER | The day of the year as a number from 1 to 365 (366 in a leap year). |
DAYSQTR |
NUMBER | The total number of days in the quarter. |
FIRSTOFMONTH |
DATE | The first day of the month for the given date. For example, if the DIMDATE row is for 08/11/2025, the FIRSTOFMONTH would be set to 08/01/2025. |
FRIWEEKEND |
DATE | The date for the following Friday of the week for the given date. For example, if the DIMDATE row is for 08/11/2025 (a Monday), FRIWEEKEND will contain the date for the following Friday, or 08/15/2025. If the DIMDATE row is already a Friday, FRIWEEKEND will contain the same date as the date for the row. For example, if the DIMDATE row is for 08/15/2025, FRIWEEKEND will also contain 08/25/2025. |
FRIWEEKSTART |
DATE | The date for the prior Saturday for the given date. For example, if the DIMDATE row is for 08/11/2025 (a Monday), FRIWEEKSTART will contain the date for the prior Saturday, or 08/09/2025. If the DIMDATE row is already a Saturday, FRIWEEKSTART will contain the same date as the date for the row. For example, if the DIMDATE row is for 08/09/2025, FRIWEEKSTART will also contain 08/09/2025. |
FULLDATEKEY |
TIMESTAMP_NTZ | A unique key representing the full date. |
ISLASTDAYOFMONTH |
NUMBER | This is set to 1 if the DIMDATE row is the last day of the month. Otherwise, contains 0. |
ISYTD |
VARCHAR | An indication of whether the date is within the current year-to-date period or not. |
LASTOFMONTH |
DATE | The last day of the month for the given date. For example, if the DIMDATE row is for 08/11/2025, the LASTOFMONTH would be set to 08/31/2025. |
MONTHNAME |
VARCHAR | The name of the month. |
MONTHOFYEAR |
NUMBER | The month of the year as a number from 1 (January) to 12 (December). |
MONTHYEAR |
VARCHAR | The month and year combined, formatted as MM/YY (for example, 1/24). |
NUMERICDATE |
NUMBER | The date represented in a numeric format, typically as YYYYMMDD. |
QTRNAME |
VARCHAR | The name of the quarter. |
QTROFYEAR |
NUMBER | The quarter of the year as a number from 1 to 4. |
RECRUITWEEKEND |
DATE | The end date of the recruitment week. |
RECRUITWEEKSTART |
DATE | The start date of the recruitment week. |
WEEKENDDATE |
DATE | The date of the last day of the week. The value is set to Saturday date. |
WEEKOFMONTH |
NUMBER | The week of the month as a number. |
WEEKOFYEAR |
NUMBER | The week of the year as a number, from 1 to 53. |
WEEKSTARTDATE |
DATE | The date of the first day of the week. The value is set to Sunday date. |
YEAROFYEAR |
NUMBER | The year represented as a number. |
YEARQUARTER |
VARCHAR | The year and quarter combined, formatted as YYYY-Q (for example, 2024-Q1). |