v_DIM_DATE
The date dimension contains information about all valid dates.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| DATEDIMID | int | BBDW.[DIM_DATE].[DATEDIMID] | |
| CALENDARYEAR | smallint | yes | BBDW.[DIM_DATE].[CALENDARYEAR] |
| CALENDARHALF | smallint | yes | BBDW.[DIM_DATE].[CALENDARHALF] |
| CALENDARHALFNAME | char(2) | yes | BBDW.[DIM_DATE].[CALENDARHALFNAME] |
| CALENDARYEARHALF | int | yes | BBDW.[DIM_DATE].[CALENDARYEARHALF] |
| CALENDARHALFYEARNAME | nvarchar(8) | yes | BBDW.[DIM_DATE].[CALENDARHALFYEARNAME] |
| CALENDARFULLHALFYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[CALENDARFULLHALFYEARNAME] |
| CALENDARQUARTER | smallint | yes | BBDW.[DIM_DATE].[CALENDARQUARTER] |
| CALENDARQUARTERNAME | char(2) | yes | BBDW.[DIM_DATE].[CALENDARQUARTERNAME] |
| CALENDARYEARQUARTER | int | yes | BBDW.[DIM_DATE].[CALENDARYEARQUARTER] |
| CALENDARQUARTERYEARNAME | nvarchar(8) | yes | BBDW.[DIM_DATE].[CALENDARQUARTERYEARNAME] |
| CALENDARFULLQUARTERYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[CALENDARFULLQUARTERYEARNAME] |
| CALENDARMONTH | smallint | yes | BBDW.[DIM_DATE].[CALENDARMONTH] |
| CALENDARMONTHNAME | nvarchar(12) | yes | BBDW.[DIM_DATE].[CALENDARMONTHNAME] |
| CALENDARWEEK | smallint | yes | BBDW.[DIM_DATE].[CALENDARWEEK] |
| CALENDARYEARWEEK | int | yes | BBDW.[DIM_DATE].[CALENDARYEARWEEK] |
| CALENDARWEEKYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[CALENDARWEEKYEARNAME] |
| CALENDARFULLWEEKYEARNAME | nvarchar(20) | yes | BBDW.[DIM_DATE].[CALENDARFULLWEEKYEARNAME] |
| CALENDARDAYOFYEAR | smallint | yes | BBDW.[DIM_DATE].[CALENDARDAYOFYEAR] |
| CALENDARYEARMONTH | int | yes | BBDW.[DIM_DATE].[CALENDARYEARMONTH] |
| CALENDARMONTHYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[CALENDARMONTHYEARNAME] |
| CALENDARFULLMONTHYEARNAME | nvarchar(20) | yes | BBDW.[DIM_DATE].[CALENDARFULLMONTHYEARNAME] |
| FISCALYEAR | smallint | yes | BBDW.[DIM_DATE].[FISCALYEAR] |
| FISCALHALF | smallint | yes | BBDW.[DIM_DATE].[FISCALHALF] |
| FISCALYEARHALF | int | yes | BBDW.[DIM_DATE].[FISCALYEARHALF] |
| FISCALHALFNAME | char(2) | yes | BBDW.[DIM_DATE].[FISCALHALFNAME] |
| FISCALHALFYEARNAME | nvarchar(8) | yes | BBDW.[DIM_DATE].[FISCALHALFYEARNAME] |
| FISCALFULLHALFYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[FISCALFULLHALFYEARNAME] |
| FISCALQUARTER | smallint | yes | BBDW.[DIM_DATE].[FISCALQUARTER] |
| FISCALQUARTERNAME | char(2) | yes | BBDW.[DIM_DATE].[FISCALQUARTERNAME] |
| FISCALYEARQUARTER | int | yes | BBDW.[DIM_DATE].[FISCALYEARQUARTER] |
| FISCALQUARTERYEARNAME | nvarchar(8) | yes | BBDW.[DIM_DATE].[FISCALQUARTERYEARNAME] |
| FISCALFULLQUARTERYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[FISCALFULLQUARTERYEARNAME] |
| FISCALPERIOD | smallint | yes | BBDW.[DIM_DATE].[FISCALPERIOD] |
| FISCALPERIODYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[FISCALPERIODYEARNAME] |
| FISCALYEARPERIOD | int | yes | BBDW.[DIM_DATE].[FISCALYEARPERIOD] |
| FISCALMONTH | smallint | yes | BBDW.[DIM_DATE].[FISCALMONTH] |
| FISCALMONTHNAME | nvarchar(12) | yes | BBDW.[DIM_DATE].[FISCALMONTHNAME] |
| FISCALYEARMONTH | int | yes | BBDW.[DIM_DATE].[FISCALYEARMONTH] |
| FISCALMONTHYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[FISCALMONTHYEARNAME] |
| FISCALFULLMONTHYEARNAME | nvarchar(20) | yes | BBDW.[DIM_DATE].[FISCALFULLMONTHYEARNAME] |
| FISCALWEEK | smallint | yes | BBDW.[DIM_DATE].[FISCALWEEK] |
| FISCALYEARWEEK | int | yes | BBDW.[DIM_DATE].[FISCALYEARWEEK] |
| FISCALWEEKYEARNAME | nvarchar(15) | yes | BBDW.[DIM_DATE].[FISCALWEEKYEARNAME] |
| FISCALFULLWEEKYEARNAME | nvarchar(20) | yes | BBDW.[DIM_DATE].[FISCALFULLWEEKYEARNAME] |
| FISCALDAYOFYEAR | smallint | yes | BBDW.[DIM_DATE].[FISCALDAYOFYEAR] |
| DAYOFMONTH | smallint | yes | BBDW.[DIM_DATE].[DAYOFMONTH] |
| DAYOFWEEK | smallint | yes | BBDW.[DIM_DATE].[DAYOFWEEK] |
| DAYNAME | nvarchar(12) | yes | BBDW.[DIM_DATE].[DAYNAME] |
| ISWEEKEND | bit | yes | BBDW.[DIM_DATE].[ISWEEKEND] |
| ISWEEKDAY | bit | yes | BBDW.[DIM_DATE].[ISWEEKDAY] |
| ISHOLIDAY | bit | yes | BBDW.[DIM_DATE].[ISHOLIDAY] |
| ISLEAPYEAR | bit | yes | BBDW.[DIM_DATE].[ISLEAPYEAR] |
| ISCURRENTDATE | bit | yes | BBDW.[DIM_DATE].[ISCURRENTDATE] |
| ISCURRENTFISCALWEEK | bit | yes | BBDW.[DIM_DATE].[ISCURRENTFISCALWEEK] |
| ISCURRENTCALENDARWEEK | bit | yes | BBDW.[DIM_DATE].[ISCURRENTCALENDARWEEK] |
| ISCURRENTMONTH | bit | yes | BBDW.[DIM_DATE].[ISCURRENTMONTH] |
| ISCURRENTPERIOD | bit | yes | BBDW.[DIM_DATE].[ISCURRENTPERIOD] |
| ISCURRENTFISCALQUARTER | bit | yes | BBDW.[DIM_DATE].[ISCURRENTFISCALQUARTER] |
| ISCURRENTCALENDARQUARTER | bit | yes | BBDW.[DIM_DATE].[ISCURRENTCALENDARQUARTER] |
| ISCURRENTFISCALYEAR | bit | yes | BBDW.[DIM_DATE].[ISCURRENTFISCALYEAR] |
| ISCURRENTCALENDARYEAR | bit | yes | BBDW.[DIM_DATE].[ISCURRENTCALENDARYEAR] |
| ISCURRENTGLFISCALYEAR | bit | yes | BBDW.[DIM_DATE].[ISCURRENTGLFISCALYEAR] |
| ISCURRENTGLFISCALPERIOD | bit | yes | BBDW.[DIM_DATE].[ISCURRENTGLFISCALPERIOD] |
| INFYSTARTTOCURRENT | bit | yes | BBDW.[DIM_DATE].[INFYSTARTTOCURRENT] |
| INCYSTARTTOCURRENT | bit | yes | BBDW.[DIM_DATE].[INCYSTARTTOCURRENT] |
| ISCLOSED | bit | yes | BBDW.[DIM_DATE].[ISCLOSED] |
| ISLASTCLOSEDDATE | bit | yes | BBDW.[DIM_DATE].[ISLASTCLOSEDDATE] |
| FILTER | bit | yes | BBDW.[DIM_DATE].[FILTER] |
| HOLIDAYNAME | nvarchar(100) | yes | BBDW.[DIM_DATE].[HOLIDAYNAME] |
| ACTUALDATE | datetime | yes | BBDW.[DIM_DATE].[ACTUALDATE] |
| ACTUALDATESTRING | nvarchar(20) | yes | BBDW.[DIM_DATE].[ACTUALDATESTRING] |
| ACTUALDATESTRINGDMY | nvarchar(25) | yes | BBDW.[DIM_DATE].[ACTUALDATESTRINGDMY] |
| ACTUALDATESTRINGMDY | nvarchar(25) | yes | BBDW.[DIM_DATE].[ACTUALDATESTRINGMDY] |
| FISCALYEARSTARTDATE | datetime | yes | BBDW.[DIM_DATE].[FISCALYEARSTARTDATE] |
| FISCALYEARENDDATE | datetime | yes | BBDW.[DIM_DATE].[FISCALYEARENDDATE] |
| FISCALQUARTERSTARTDATE | datetime | yes | BBDW.[DIM_DATE].[FISCALQUARTERSTARTDATE] |
| FISCALQUARTERENDDATE | datetime | yes | BBDW.[DIM_DATE].[FISCALQUARTERENDDATE] |
| FISCALWEEKSTARTDATE | datetime | yes | BBDW.[DIM_DATE].[FISCALWEEKSTARTDATE] |
| FISCALWEEKENDDATE | datetime | yes | BBDW.[DIM_DATE].[FISCALWEEKENDDATE] |
| CALENDARQUARTERSTARTDATE | datetime | yes | BBDW.[DIM_DATE].[CALENDARQUARTERSTARTDATE] |
| CALENDARQUARTERENDDATE | datetime | yes | BBDW.[DIM_DATE].[CALENDARQUARTERENDDATE] |
| CALENDARWEEKSTARTDATE | datetime | yes | BBDW.[DIM_DATE].[CALENDARWEEKSTARTDATE] |
| CALENDARWEEKENDDATE | datetime | yes | BBDW.[DIM_DATE].[CALENDARWEEKENDDATE] |
| PERIODSTARTDATE | datetime | yes | BBDW.[DIM_DATE].[PERIODSTARTDATE] |
| PERIODENDDATE | datetime | yes | BBDW.[DIM_DATE].[PERIODENDDATE] |
| MONSEQUENCE | smallint | yes | BBDW.[DIM_DATE].[MONSEQUENCE] |
| TUESEQUENCE | smallint | yes | BBDW.[DIM_DATE].[TUESEQUENCE] |
| WEDSEQUENCE | smallint | yes | BBDW.[DIM_DATE].[WEDSEQUENCE] |
| THUSEQUENCE | smallint | yes | BBDW.[DIM_DATE].[THUSEQUENCE] |
| FRISEQUENCE | smallint | yes | BBDW.[DIM_DATE].[FRISEQUENCE] |
| SATSEQUENCE | smallint | yes | BBDW.[DIM_DATE].[SATSEQUENCE] |
| SUNSEQUENCE | smallint | yes | BBDW.[DIM_DATE].[SUNSEQUENCE] |
| DAYSSINCE | int | yes | BBDW.[DIM_DATE].[DAYSSINCE] |
| WEEKSSINCE | int | yes | BBDW.[DIM_DATE].[WEEKSSINCE] |
| MONTHSSINCE | int | yes | BBDW.[DIM_DATE].[MONTHSSINCE] |
| QUARTERSSINCE | int | yes | BBDW.[DIM_DATE].[QUARTERSSINCE] |
| YEARSSINCE | int | yes | BBDW.[DIM_DATE].[YEARSSINCE] |
| CALENDARYEARSSINCE | int | yes | BBDW.[DIM_DATE].[CALENDARYEARSSINCE] |
| GLFISCALYEARSSINCE | int | yes | BBDW.[DIM_DATE].[GLFISCALYEARSSINCE] |
| GLFISCALPERIODSSINCE | int | yes | BBDW.[DIM_DATE].[GLFISCALPERIODSSINCE] |
| SEQUENCE | int | BBDW.[DIM_DATE].[SEQUENCE] | |
| Calendar Date Calculations | varchar(12) | BBDW.[DIM_DATE].[Calendar Date Calculations] | |
| Fiscal Date Calculations | varchar(12) | BBDW.[DIM_DATE].[Fiscal Date Calculations] | |
| GLFISCALYEAR | nvarchar(12) | yes | BBDW.[DIM_DATE].[TITLE2] |
| GLFISCALYEARDESCRIPTION | nvarchar(60) | yes | BBDW.[DIM_DATE].[SUFFIX2] |
| GLFISCALYEARSTATUS | nvarchar(10) | yes | BBDW.[DIM_DATE].[MAIDENNAME] |
| GLFISCALYEARSTARTDATE | datetime | yes | BBDW.[DIM_DATE].[GLFISCALYEARSTARTDATE] |
| GLFISCALYEARENDDATE | datetime | yes | BBDW.[DIM_DATE].[GLFISCALYEARENDDATE] |
| GLFISCALYEARSEQUENCE | int | yes | BBDW.[DIM_DATE].[GLFISCALYEARSEQUENCE] |
| GLFISCALPERIODSINYEAR | int | yes | BBDW.[DIM_DATE].[GLFISCALPERIODSINYEAR] |
| GLFISCALPERIOD | int | yes | BBDW.[DIM_DATE].[ISINDIVIDUAL] |
| GLFISCALPERIODSEQUENCE | int | yes | BBDW.[DIM_DATE].[GLFISCALPERIODSEQUENCE] |
| GLFISCALPERIODSTARTDATE | datetime | yes | BBDW.[DIM_DATE].[GLFISCALPERIODSTARTDATE] |
| GLFISCALPERIODENDDATE | datetime | yes | BBDW.[DIM_DATE].[GLFISCALPERIODENDDATE] |
| GLFISCALPERIODCLOSED | bit | yes | BBDW.[DIM_DATE].[ISHOUSEHOLD] |
Definition
Copy
CREATE view [BBDW].[v_DIM_DATE] as
select
[DATEDIMID],
[CALENDARYEAR],
[CALENDARHALF],
[CALENDARHALFNAME],
[CALENDARYEARHALF],
[CALENDARHALFYEARNAME],
[CALENDARFULLHALFYEARNAME],
[CALENDARQUARTER],
[CALENDARQUARTERNAME],
[CALENDARYEARQUARTER],
[CALENDARQUARTERYEARNAME],
[CALENDARFULLQUARTERYEARNAME],
[CALENDARMONTH],
[CALENDARMONTHNAME],
[CALENDARWEEK],
[CALENDARYEARWEEK],
[CALENDARWEEKYEARNAME],
[CALENDARFULLWEEKYEARNAME],
[CALENDARDAYOFYEAR],
[CALENDARYEARMONTH],
[CALENDARMONTHYEARNAME],
[CALENDARFULLMONTHYEARNAME],
[FISCALYEAR],
[FISCALHALF],
[FISCALYEARHALF],
[FISCALHALFNAME],
[FISCALHALFYEARNAME],
[FISCALFULLHALFYEARNAME],
[FISCALQUARTER],
[FISCALQUARTERNAME],
[FISCALYEARQUARTER],
[FISCALQUARTERYEARNAME],
[FISCALFULLQUARTERYEARNAME],
[FISCALPERIOD],
[FISCALPERIODYEARNAME],
[FISCALYEARPERIOD],
[FISCALMONTH],
[FISCALMONTHNAME],
[FISCALYEARMONTH],
[FISCALMONTHYEARNAME],
[FISCALFULLMONTHYEARNAME],
[FISCALWEEK],
[FISCALYEARWEEK],
[FISCALWEEKYEARNAME],
[FISCALFULLWEEKYEARNAME],
[FISCALDAYOFYEAR],
[DAYOFMONTH],
[DAYOFWEEK],
[DAYNAME],
[ISWEEKEND],
[ISWEEKDAY],
[ISHOLIDAY],
[ISLEAPYEAR],
[ISCURRENTDATE],
[ISCURRENTFISCALWEEK],
[ISCURRENTCALENDARWEEK],
[ISCURRENTMONTH],
[ISCURRENTPERIOD],
[ISCURRENTFISCALQUARTER],
[ISCURRENTCALENDARQUARTER],
[ISCURRENTFISCALYEAR],
[ISCURRENTCALENDARYEAR],
[ISCURRENTGLFISCALYEAR],
[ISCURRENTGLFISCALPERIOD],
[INFYSTARTTOCURRENT],
[INCYSTARTTOCURRENT],
[ISCLOSED],
[ISLASTCLOSEDDATE],
[FILTER],
[HOLIDAYNAME],
[ACTUALDATE],
[ACTUALDATESTRING],
[ACTUALDATESTRINGDMY],
[ACTUALDATESTRINGMDY],
[FISCALYEARSTARTDATE],
[FISCALYEARENDDATE],
[FISCALQUARTERSTARTDATE],
[FISCALQUARTERENDDATE],
[FISCALWEEKSTARTDATE],
[FISCALWEEKENDDATE],
[CALENDARQUARTERSTARTDATE],
[CALENDARQUARTERENDDATE],
[CALENDARWEEKSTARTDATE],
[CALENDARWEEKENDDATE],
[PERIODSTARTDATE],
[PERIODENDDATE],
[MONSEQUENCE],
[TUESEQUENCE],
[WEDSEQUENCE],
[THUSEQUENCE],
[FRISEQUENCE],
[SATSEQUENCE],
[SUNSEQUENCE],
[DAYSSINCE],
[WEEKSSINCE],
[MONTHSSINCE],
[QUARTERSSINCE],
[YEARSSINCE],
[CALENDARYEARSSINCE],
[GLFISCALYEARSSINCE],
[GLFISCALPERIODSSINCE],
[SEQUENCE],
'Current Date' as [Calendar Date Calculations],
'Current Date' as [Fiscal Date Calculations],
[GLFISCALYEAR],
[GLFISCALYEARDESCRIPTION],
[GLFISCALYEARSTATUS],
[GLFISCALYEARSTARTDATE],
[GLFISCALYEARENDDATE],
[GLFISCALYEARSEQUENCE],
[GLFISCALPERIODSINYEAR],
[GLFISCALPERIOD],
[GLFISCALPERIODSEQUENCE],
[GLFISCALPERIODSTARTDATE],
[GLFISCALPERIODENDDATE],
[GLFISCALPERIODCLOSED]
from BBDW.[DIM_DATE]