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.
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.
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".
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.
|
Back to Top