Dict-Physical

View VMD_Date

Card of view VMD_Date

Name	VMD_Date
Comment	See documentation on MD_Date table in the DimsTime package.
Dimensional Type	Dimension

SQL query of the view VMD_Date

SELECT
   CONVERT(int, [MD_DATE_DATEINTEGER]) AS MD_DATE_DATEINTEGER,
   MD_DATE_SQLDATE,
   MD_DATE_DDMONYYYY,
   MD_DATE_YYYYMMDD,
   MD_DATE_YYYYMM,
   CONVERT(int, [MD_DATE_YEAR]) AS MD_DATE_YEAR,
   CONVERT(varchar(7), [MD_DATE_QUARTER]) AS MD_DATE_QUARTER,
   MD_DATE_MONTHOFFISCALYEAR,
   MD_DATE_MONTHOFQUARTER,
   MD_DATE_MONTHOFFISCALQUARTER,
   MD_DATE_WEEKOFFISCALYEAR,
   MD_DATE_WEEKOFACADEMICYEAR,
   MD_DATE_WEEKOFFISCALQUARTER,
   MD_DATE_DAYOFFISCALYEAR,
   MD_DATE_DAYOFACADEMICYEAR,
   MD_DATE_DAYOFQUARTER,
   MD_DATE_MONTHNUM,
   CONVERT(varchar(9), [MD_DATE_MONTHNAME]) AS MD_DATE_MONTHNAME,
   CONVERT(char(3), [MD_DATE_MONTHNAMEABBREV]) AS MD_DATE_MONTHNAMEABBREV,
   MD_DATE_DAYOFMONTH,
   MD_DATE_DAYOFWEEKNUM,
   CONVERT(char(9), [MD_DATE_DAYOFWEEKNAME]) AS MD_DATE_DAYOFWEEKNAME,
   CONVERT(char(3), [MD_DATE_DAYOFWEEKNAMEABBREV]) AS MD_DATE_DAYOFWEEKNAMEABBREV,
   MD_DATE_DAYOFYEAR,
   MD_DATE_WEEKOFYEAR,
   MD_DATE_WEEKOFQUARTER,
   CONVERT(char(7), [MD_DATE_TERMNAME]) AS MD_DATE_TERMNAME,
   CONVERT(char(7), [MD_DATE_ACADEMICYEAR]) AS MD_DATE_ACADEMICYEAR,
   MD_DATE_TERMNUM,
   MD_DATE_WEEKOFTERM,
   MD_DATE_DAYOFTERM,
   CONVERT(int, [MD_DATE_FISCALYEAR]) AS MD_DATE_FISCALYEAR,
   CONVERT(varchar(7), [MD_DATE_FISCALQUARTER]) AS MD_DATE_FISCALQUARTER,
   MD_DATE_DAYOFFISCALQUARTER,
   CONVERT(char(3), [MD_DATE_ISUSCIVILHOLIDAY]) AS MD_DATE_ISCIVILHOLIDAY,
   CONVERT(char(3), [MD_DATE_ISWEEKDAY]) AS MD_DATE_ISWEEKDAY,
   CONVERT(char(3), [MD_DATE_ISFIRSTDAYOFQUARTER]) AS MD_DATE_ISFIRSTDAYOFQUARTER,
   CONVERT(char(3), [MD_DATE_ISFIRSTDAYOFFISCALQUARTER]) AS MD_DATE_ISFIRSTDAYOFFISCALQUARTER,
   CONVERT(varchar(7), [MD_DATE_ISFIRSTDAYOFTERM]) AS MD_DATE_ISFIRSTDAYOFTERM,
   CONVERT(char(3), [MD_DATE_ISLASTDAYOFMONTH]) AS MD_DATE_ISLASTDAYOFMONTH,
   CONVERT(char(3), [MD_DATE_ISLASTDAYOFFISCALQUARTER]) AS MD_DATE_ISLASTDAYOFFISCALQUARTER,
   CONVERT(char(3), [MD_DATE_ISLASTDAYOFFISCALYEAR]) AS MD_DATE_ISLASTDAYOFFISCALYEAR,
   CONVERT(char(3), [MD_DATE_ISLASTDAYOFYEAR]) AS MD_DATE_ISLASTDAYOFYEAR,
   CONVERT(varchar(7), [MD_DATE_ISLASTDAYOFTERM]) AS MD_DATE_ISLASTDAYOFTERM,
   CONVERT(char(3), [MD_DATE_ISLASTDAYOFQUARTER]) AS MD_DATE_ISLASTDAYOFQUARTER,
   CONVERT(char(3), [MD_DATE_ISLASTDAYOFWEEK]) AS MD_DATE_ISLASTDAYOFWEEK,
   CONVERT(varchar(7), [MD_DATE_ISREGISTRARCENSUSDAY]) AS MD_DATE_ISREGISTRARCENSUSDAY,
   CONVERT(char(3), [MD_DATE_ISFISCALYTD]) AS MD_DATE_ISFISCALYTD,
   CONVERT(char(3), [MD_DATE_ISCALENDARYTD]) AS MD_DATE_ISCALENDARYTD,
   CONVERT(char(3), [MD_DATE_ISFISCALQTD]) AS MD_DATE_ISFISCALQTD,
   CONVERT(varchar(7), [MD_DATE_ISTERMTODATE]) AS MD_DATE_ISTERMTODATE
FROM
MD_DATE

List of incoming view references of the view VMD_Date

ViewReference_4

VMF_AwardAmountSnapshot

ViewReference_7

VMF_TopSATScores

ViewReference_9

VMF_ReaderRating

ViewReference_17

VMF_AwardAmountCurrent

ViewReference_39

VMF_GLFact

ViewReference_80

VMF_SurveyCellData

ViewReference_108

VMF_IssueTrackingTicket

ViewReference_122

VMF_IssueTrackingAssetValue

ViewReference_134

VMF_Booking

ViewReference_138

VMF_RoomUtilization

ViewReference_149

VMF_RoomAssignment

ViewReference_150

VMF_RoomUtilizationByDay

ViewReference_171

VMF_LeaveAccrual

ViewReference_184

VMF_ReadingByHourAccumulating

ViewReference_196

VMF_ReadingByHourSnapshot

ViewReference_211

VMF_ReadingByHourNoAggregations

ViewReference_214

VMF_CardTransaction

ViewReference_229

VMF_KeyedComputerEvent

ViewReference_237

VMF_KeyedComputerInUse

ViewReference_242

VMF_KeyedComputerInUseWithRoomUtilization

List of referenced objects of the view VMD_Date

MD_Date

List of shortcuts of the view VMD_Date

View

View

View

View

View

View

View

View

View

List of diagrams containing the view VMD_Date

ViewsProspectDiagram

List of permissions of the view VMD_Date

SELECT,VIEW DEFINITION

BusinessReader

SELECT,VIEW DEFINITION

CardAccessReader

SELECT,VIEW DEFINITION

EnergyReader

SELECT,VIEW DEFINITION

FinancialAidReader

SELECT,VIEW DEFINITION

IssueTrackingReader

SELECT,VIEW DEFINITION

ProspectViewReader

SELECT,VIEW DEFINITION

SpaceReader

SELECT,VIEW DEFINITION

StudentReader

SELECT,VIEW DEFINITION

SurveyReader

List of view columns of the view VMD_Date

MD_DATE_DATEINTEGER

MD_Date_SQLDate

datetime

SQL date - defaults to the max possible date, 12 Dec 9999 (which should be treated as equivalent to "Unknown" or "not specified").

MD_Date_DDMonYYYY

char(11)

Date formatted like "31 Dec 2008"

MD_Date_YYYYMMDD

char(8)

Date in YYYYMMDD format, e.g., 20081231 (= 31 Dec 2008). Defaults to "Unknown."

MD_Date_YYYYMM

char(7)

Date in YYYYMM format, e.g., 200812 (= Dec 2008). Defaults to "Unknown."

MD_DATE_YEAR

MD_DATE_QUARTER

MD_Date_MonthOfFiscalYear

int

Ordinal number of month, starting from the beginning of the fiscal year (July). E.g., September would be 3.

Note that the fiscal year doubles also as the financial aid year, and may also be used for certain official academic-year reporting that needs to include a summer term and that groups this term with the following academic year (as opposed to grouping it with the previous academic year, which matches the usual informal concept of an academic year).

MD_Date_MonthOfQuarter

int

Ordinal month of calendar quarter. E.g., April would be month 2 (of quarter 2).

MD_Date_MonthOfFiscalQuarter

int

Ordinal month of fiscal quarter. E.g., October would be month 1 of fiscal quarter 2.

MD_Date_WeekOfFiscalYear

int

Ordinal week of fiscal year (e.g., July 1-7 would be week 1).

Note that the fiscal year doubles also as the financial aid year, and may also be used for certain official academic-year reporting that needs to include a summer term and that groups this term with the following academic year (as opposed to grouping it with the previous academic year, which matches the usual informal concept of an academic year).

MD_Date_WeekOfAcademicYear

int

Ordinal number of week in academic year (starting with 1).

Note well: In Colleague, academic years (AY terms) are defined as beginning in September, and ending in July. HOWEVER, fiscally speaking (and also from a financial aid, and often a credit, standpoint) summer term goes with the following academic year. Realistically, though, because Fall is traditionally the start of the academic year at Carleton, and because most students, professors, and regular folk all think of the fall term as term 1, we begin counting weeks of the academic year in the fall, not the summer. If you would like to report based on weeks starting in July, use the fiscal-year week attributes.

MD_Date_WeekOfFiscalQuarter

int

MD_Date_DayOfFiscalYear

int

Ordinal day in fiscal year, starting with July 1. E.g., July 3 would be 3. August 1 would be 32.

Note that the fiscal year doubles also as the financial aid year, and may also be used for certain official academic-year reporting that needs to include a summer term and that groups this term with the following academic year (as opposed to grouping it with the previous academic year, which matches the usual informal concept of an academic year).

MD_Date_DayOfAcademicYear

int

Ordinal day number, counting from the start of the academic year (as determined by the Registrar).

Note well: In Colleague, academic years (AY terms) are defined as beginning in September, and ending in July. HOWEVER, fiscally speaking (and also from a financial aid, and often a credit standpoint) summer term goes with the following academic year. Realistically, though, because Fall is traditionally the start of the academic year at Carleton, and because most students, professors, and regular folk all think of the fall term as term 1, we begin counting days in the academic year in the fall, not the summer.

MD_Date_DayOfQuarter

int

Ordinal number of the day in its quarter. E.g., Apr 1 begins calendar quarter 2. Hence DayOfCalendarQuarter for Apr 1 = 1.

MD_Date_MonthNum

int

Ordinal number of month in year. E.g., March would be 3.

MD_DATE_MONTHNAME

MD_DATE_MONTHNAMEABBREV

MD_Date_DayOfMonth

int

MD_Date_DayOfWeekNum

int

Number of the day of the week (Sun = 1, Sat = 7).

MD_DATE_DAYOFWEEKNAME

MD_DATE_DAYOFWEEKNAMEABBREV

MD_Date_DayOfYear

int

Ordinal number of a given day in its year. For example, if it's January 1st, then DayOfYear is 1. On January second, it's 2, and so on, through day 365 (Dec 31).

MD_Date_WeekOfYear

int

Ordinal number of week in year for a given date. For example, if it's January 1st, we must be in week 1 of the current year. Hence WeekOfYear would be 1. On Sunday of the next week, though, the WeekOfYear would be 2. A WeekOfYear of 9999 means "Unknown."

MD_Date_WeekOfQuarter

int

MD_DATE_TERMNAME

MD_DATE_ACADEMICYEAR

MD_Date_TermNum

int

Number of term in year (1-4). Defaults to 9999.

Follows traditional numbering, in which fall is term 1. This is not the same as the fiscal or financial aid numbering, in which summer quarter is actually term 1. Having summer quarter (which is rarely used at Carleton) be term 1 is counterintuitive for most reports, for most non-specialist audiences. Keep this in mind.

MD_Date_WeekOfTerm

int

MD_Date_DayOfTerm

int

MD_DATE_FISCALYEAR

MD_DATE_FISCALQUARTER

MD_Date_DayOfFiscalQuarter

int

Ordinal number of the day in its fiscal quarter. E.g., Oct 1 begins fiscal quarter 2. Hence DayOfFiscalQuarter for October 1 = 1 (unless Carleton's fiscal calendar changes).

MD_DATE_ISCIVILHOLIDAY

MD_DATE_ISWEEKDAY

MD_DATE_ISFIRSTDAYOFQUARTER

MD_DATE_ISFIRSTDAYOFFISCALQUARTER

MD_DATE_ISFIRSTDAYOFTERM

MD_DATE_ISLASTDAYOFMONTH

MD_DATE_ISLASTDAYOFFISCALQUARTER

MD_DATE_ISLASTDAYOFFISCALYEAR

MD_DATE_ISLASTDAYOFYEAR

MD_DATE_ISLASTDAYOFTERM

MD_DATE_ISLASTDAYOFQUARTER

MD_DATE_ISLASTDAYOFWEEK

MD_DATE_ISREGISTRARCENSUSDAY

MD_DATE_ISFISCALYTD

MD_DATE_ISCALENDARYTD

MD_DATE_ISFISCALQTD

1 2

Home