DARTS : Effective Date

Effective Date is used in a number of tables. It allows for input today of information to be used at a later date. It helps to keep track of the history of changes. The data becomes "effective" or current as of that date. For more information, see our Data Dictionary for Effective Dates.

Effective date is often used with Effective Status and/ or Effective sequence fields.

Example for a Table with Effective Date

The data in the table below is a part of ACAD_PROG_TBL, which contains information about academic programs.

There are three rows with ACAD_PROG= HUDS on this table. The effective date (EFFDT) of 1/1/1901 is usually the default date.

On EFFDT = 11/1/2006 (second row), it changed the DESCR field from "Human Development" to "Human Development-MS". It means the description (DESCR) of this program from the beginning of the program to 10/31/2006 was "Human Development".

On 9/3/2013, the new row was added since some data about this program has changed. (It is not shown the table below, however, you ,may see data change if you pull the entire table.)

If you would like to pull the most recent data about this program, pull the data for EFFDT =9/3/2013 (last row).

INSTITUTION	ACAD_PROG	EFFDT	EFF_STATUS	DESCR	DESCRSHORT
UMS05	HUDS	1/1/1901	A	Human Development	HUD MS
UMS05	HUDS	11/1/2006	A	Human Development-MS	HUD MS
UMS05	HUDS	9/3/2013	A	Human Development-MS	HUD MS

Using a Table with Effective date in a Query

When you add a table that makes use of an Effective Date to a query, you will be notified that query has automatically added effective date criteria.

effective date criteria added
After clicking OK button, you will see the criteria as "Eff Date <= Current Date" on the "Criteria" page. This is the default criteria to pull the most recent record from the table. Only the last row created on the effective date will be included in the query results.

NOTE:

When you use left outer join, the effective date criteria is not apply to the specific table. Go to Criteria page, and click "Edit" for the effective date criteria. You can change dropdown for "This criteria belongs to".

edit belong to
belong to changed

Back to Top

Change Condition Type

You can always change, add, or delete these options from within the Criteria page. The system 'knows' that the criteria is attached to an effective date fields so the Condition Type list is expanded to include the above comparisons.

You have the option of comparing the Effective Date to either:

Current Date – today's date
Expression – some date you'll type in
Field – a date from some other table.

Generally you'll use Current Date. Here are the other Effective Date Options you might like to use:

History	Effective Date <	Date you are comparing to is later than the Effective Date
History and/or Current	Effective Date <=	Date you are comparing to is either the same or later than the Effective Date.
Current and/or Future	Effective Date >=	Date you are comparing to is either the same or earlier than the Effective Date
Future	Effective Date >	Date you are comparing to is earlier than the Effective Date
Earliest	First Effective Date	The Effective Date farthest back in history.
Latest	Last Effective Date	The Effective Date farthest into the future.
None	No Effective Date Option	Builds no criteria for Effective Date.

Back to Top

HELP IS AT HAND!

Question, comment, critique? We are here to help and we strive to make our site a comprehensive, user friendly experience. Contact us at DARTS@maine.edu.

DARTS : Effective Date

Example for a Table with Effective Date

Using a Table with Effective date in a Query

Change Condition Type

Attachments: