Effective Date

Body

This article provides information regarding Effective Dates in PeopleSoft.

Detailed Information

Effective Date is used in a number of tables, and allows for input today of information to be used at a later date. It also 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 an 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 the query has automatically added effective date criteria.

showing the effective date criteria added message that will appear when you use a table with effective dating.
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.
showing an example of the effective date criteria listed on the Criteria Page with the default setting.

INFO: When you use a left outer join, the effective date criteria are not applied to the specific table. Go to the Criteria page, and click "Edit" for the effective date criteria. You can change the drop-down for "This criteria belongs to".

showing the edit criteria properties with the This Criteria belongs to dropdown highlighted.

showing an example of where the belongs to information has been changed for Effective Date.

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 are attached to an effective date field, 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 the Current Date. Here are the other Effective Date Options you might like to use:

Time Period	Code	Description
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 the Effective Date.