Dict-Physical

Table MD_Date

Card of table MD_Date

Name	MD_Date
Dimensional Type	Dimension
Comment	Standard date dimension, with one row for each day. Days have all the usual attributes, such as what year they are in, what month; whether they are a weekday; and so on. The default "Unknown" date is 9999-12-31, i.e., 31 Dec 9999. Note that the AuditKey and Timestamp attributes are for internal use only, and not for reporting.

Check constraint name of the table MD_Date

CKT_MD_DATE

List of incoming references of the table MD_Date

AssetValueToDate_Disposal

MF_IssueTrackingAssetValue

MD_Date_DisposalDate

AssetValueToDate_Purchase

MF_IssueTrackingAssetValue

MD_Date_PurchaseDate

BookingToBookingEndDate

MF_Booking

MD_Date_BookingEndDate

BookingToBookingStartDate

MF_Booking

MD_Date_BookingStartDate

BookingToEventEndDate

MF_Booking

MD_Date_EventEndDate

BookingToEventStartDate

MF_Booking

MD_Date_EventStartDate

GiftDateAdded

MF_Gift

MD_Date_DateIntegerAdded

GiftDateReceiptGenerated

MF_Gift

MD_Date_DateIntegerReceipt

GiftDateReceived

MF_Gift

MD_Date_DateIntegerReceived

Relationship_2

MF_ProspectActivity

MD_Date_DateInteger

Relationship_8

MF_ReaderRating

MD_Date_DateInteger

Relationship_10

MF_TopSATScores

MD_Date_DateInteger

Relationship_15

MF_Rate

MD_Date_DateInteger

Relationship_21

MF_AwardAmount

MD_Date_DateInteger

Relationship_31

MF_GLFact

MD_Date_DateInteger

Relationship_64

MF_BenefitDates

MD_Date_DateInteger

Relationship_67

MF_BenefitCost

MD_BenCost_StartDate

Relationship_80

MF_SectionMeetsAt

MD_Date_DateInteger

Relationship_84

MF_SurveyCellData

MD_Date_DateInteger

Relationship_96

MF_ReadingByHourNoAggregations

MD_Date_DateInteger

Relationship_98

MF_UnitCost

MD_Date_DateInteger

Relationship_107

MF_ReadingByHourSnapshot

MD_Date_DateInteger

Relationship_140

MF_RoomAssignment

MD_Date_AssignmentStart

Relationship_141

MF_RoomAssignment

MD_Date_AssignmentEnd

Relationship_152

MF_BenefitEmployeeContribution

MD_Date_DateInteger

Relationship_153

MF_BenefitCarletonContribution

MD_Date_DateInteger

Relationship_154

MF_LeaveAccrual

MD_Date_DateInteger

Relationship_167

MF_AlumActivityParticipation

MD_Date_DateIntegerStart

Relationship_169

MF_AlumEventParticipation

MD_Date_DateInteger

Relationship_175

MF_AlumActivityParticipation

MD_Date_DateIntegerEnd

Relationship_179

MF_AlumContact

MD_Date_DateInteger

Relationship_210

MF_AlumEducation

MD_Date_DateInteger

Relationship_211

MF_AlumEmployment

MD_Date_DateIntegerStart

Relationship_212

MF_AlumEmployment

MD_Date_DateIntegerEnd

Relationship_222

MF_ReadingByHourAccumulating

MD_Date_DateInteger

Relationship_233

MF_CardTransaction

MD_Date_DateInteger

Relationship_240

MF_Advisement

MD_Date_StartDate

Relationship_241

MF_Advisement

MD_Date_EndDate

Relationship_243

MF_KeyedComputerInUse

MD_Date_DateInteger

Relationship_248

MF_KeyedComputerSoftwareUsage

MD_Date_DateInteger

Relationship_264

MF_KeyedComputerEvent

MD_Date_DateInteger

Relationship_272

MF_KeyedComputerEvent

MD_Date_DateIntegerOther

Relationship_CloseDate

MF_IssueTrackingTicket

MF_ITTicket_CloseDate

Relationship_FirstResponseDate

MF_IssueTrackingTicket

MF_ITTicket_FirstResponseDate

Relationship_ReportDate

MF_IssueTrackingTicket

MF_ITTicket_ReportDate

Relationship_WorkEndDate

MF_IssueTrackingTicket

MF_ITTicket_WorkEndDate

Relationship_WorkStartDate

MF_IssueTrackingTicket

MF_ITTicket_WorkStartDate

List of referencing views of the table MD_Date

VMD_DatesOnOrAroundCensusTime

VMD_DATESONORAROUNDCENSUSTIME

VMD_FiscalYear

VMD_FISCALYEAR

VMF_BookingSnapshotWithOverlap

VMF_BOOKINGSNAPSHOTWITHOVERLAP

VMF_RoomAssignmentSnapshot

VMF_ROOMASSIGNMENTSNAPSHOT

VMF_RoomAssignmentToPersonBridge

VMF_ROOMASSIGNMENTTOPERSONBRIDGE

VMF_SlotSnapshotWithOverlap

VMF_SLOTSNAPSHOTWITHOVERLAP

VW_GLInfoWithDynamicSecurity

VW_GLINFOWITHDYNAMICSECURITY

List of diagrams containing the table MD_Date

DimsTimeDiagram

List of columns of the table MD_Date

MD_Date_DateInteger

Integer representation of the date (e.g., 20070101 for 1 Jan 2007). Defaults to 99991231, for situations where a date is required but none is available.

DW_DateInteger

int

MD_Date_SQLDate

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

<None>

datetime

MD_Date_DDMonYYYY

Date formatted like "31 Dec 2008"

<None>

char(11)

MD_Date_YYYYMMDD

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

<None>

char(8)

MD_Date_YYYYMM

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

<None>

char(7)

MD_Date_Year

Year as an integer (i.e., a number, not a string of characters, e.g., "00" or "2008"). The year 9999 indicates a default ("Unknown") date.

DW_YearAtCarleton

int

MD_Date_Quarter

DW_Quarter

varchar(7)

MD_Date_MonthOfFiscalYear

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).

<None>

int

MD_Date_MonthOfQuarter

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

<None>

int

MD_Date_MonthOfFiscalQuarter

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

<None>

int

MD_Date_WeekOfFiscalYear

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).

<None>

int

MD_Date_WeekOfAcademicYear

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.

<None>

int

MD_Date_WeekOfFiscalQuarter

<None>

int

MD_Date_DayOfFiscalYear

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).

<None>

int

MD_Date_DayOfAcademicYear

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.

<None>

int

MD_Date_DayOfQuarter

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

<None>

int

MD_Date_MonthNum

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

<None>

int

MD_Date_MonthName

DW_MonthName

varchar(9)

MD_Date_MonthNameAbbrev

DW_MonthNameAbbrev

char(3)

MD_Date_DayOfMonth

<None>

int

MD_Date_DayOfWeekNum

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

<None>

int

MD_Date_DayOfWeekName

DW_DayOfWeekName

char(9)

MD_Date_DayOfWeekNameAbbrev

DW_DayOfWeekNameAbbrev

char(3)

MD_Date_DayOfYear

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).

<None>

int

MD_Date_WeekOfYear

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."

<None>

int

MD_Date_WeekOfQuarter

<None>

int

MD_Date_TermName

Academic term name (e.g., 08/FA for fall of 2008). The values here are standard ones used on Colleague. Technical note: There is also a term dimension.

DW_TermShortName

char(7)

MD_Date_AcademicYear

The academic year in which a given day lies. The format for academic years, lately, in the Office of the Dean of the College at Carleton, has been XXXX-YY (e.g., 2009-10). We retain that format here. This attribute defaults to 'Unknown' if the academic year start date and end dates are not known.

Note well: In Colleague, academic years (AY terms) are defined as beginning in September, and ending in July. This matches the IPEDS definition of an academic year, which is the period of time generally extending from September to June; usually equated to 2 semesters or trimesters, 3 quarters, or the period covered by a 4-1-4 calendar system.

Fiscally speaking (and also from a financial aid, and often a credit standpoint) summer term goes with the following academic year. But 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 group the summer term here with the previous academic year. If you want the summer term grouped with the following academic year, use the fiscal-year reporting hierarchy, which begins in July. Really, though, if one is using the normal IPEDS definition of an academic year, the summer term is typically excluded, or marginalized.

DW_AcademicYear

char(7)

MD_Date_TermNum

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.

<None>

int

MD_Date_WeekOfTerm

<None>

int

MD_Date_DayOfTerm

<None>

int

MD_Date_FiscalYear

Integer fiscal year (2008, 2009, etc.). Note that at Carleton, the fiscal year extending from 1 July 2008 to 30 June 2009 is fiscal year 2009. This contrasts with academic years, which are traditionally designated using a YYYY-YY format (e.g., 2008-09). Fiscal year of 9999 should be taken as "Unknown."

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).

DW_YearAtCarleton

int

MD_Date_FiscalQuarter

Quarter of fiscal year. E.g., July through September would be fiscal quarter 1.

This is not to be confused with terms, or with calendar quarters.

Note, in general, 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). So fiscal quarter 1 is also financial aid quarter 1. And term 1 of the academic year (fall) is actually term 2 of the fiscal year (and also of the financial aid year).

DW_Quarter

varchar(7)

MD_Date_DayOfFiscalQuarter

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).

<None>

int

MD_Date_IsUSCivilHoliday

Not populated currently (i.e., every row has the same value, "No"). When implemented (if we find we need it) we will populate days that represent US civil holidays with a "Yes."

The reason this attribute is not populated is that Carleton's schedule has little to do with the normal holiday schedule (with our system of minimal institutional holidays, combined with floating holiday allotments). If we populate anything, it would be a list of Carleton holidays. But because these vary year by year, and because holidays are not used in any key reports, we do not provide this information, as yet.

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsWeekday

'Yes' if a given day is Mon-Fri (not Sat/Sun).

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsFirstDayOfQuarter

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsFirstDayOfFiscalQuarter

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsFirstDayOfTerm

DW_YesNoOrUnknown

varchar(7)

MD_Date_IsLastDayOfMonth

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsLastDayOfFiscalQuarter

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsLastDayOfFiscalYear

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsLastDayOfYear

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsLastDayOfTerm

DW_YesNoOrUnknown

varchar(7)

MD_Date_IsLastDayOfQuarter

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsLastDayOfWeek

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsRegistrarCensusDay

'Yes' if a given day is a Registrar census day. This day is important for various institutional calculations we do. It is the official "as of" date for a number of values we report on.

Note, though, that the value here is calculated. For various operational reasons, the real census date on any given term may vary from the calculated date given here. A mechanism should be developed for recording the actual date here, but this has not as yet happened.

DW_YesNoOrUnknown

varchar(7)

MD_Date_IsFiscalYTD

"Yes" if a given date should be factored into fiscal YTD calculations. Keep in mind YTD here does apply to the fiscal year, not the calendar year. There is another attribute for calendar YTD calculations.

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsCalendarYTD

"Yes" if a given date should be factored into calendar YTD calculations. Keep in mind, YTD here does apply to the calendar year, not the fiscal year. There is another attribute for fiscal YTD calculations.

DW_YesOrNoNOTNULL

char(3)

MD_Date_IsFiscalQTD

DW_YesOrNoNOTNULL

char(3)

1 2

Home