WORKING WITH DATES
A number of the articles in this section, are related to dates, including a number of demonstrations of Effective Dating – how to use them in criteria, how to use them with prompts, etc.
Among them is an article on using today’s date (SYSDATE) in your queries entitled Today’s Date in Query.
Dates are an important element when entering information into the pages/panels of SFIS and when extracting information through Query. The concept of the date in SFIS encompasses birth dates, admit terms, academic years, class year, today’s date, months, calendar years, and so on, and also the Effective Date. Dates can be entered through a process or manually entered.
Dates can be used in Query to run computations over spans of time; they can serve to exhibit history; using expressions and Wes Functions, dates can be displayed in numerous formats. Note that the proper use of dates has an impact throughout the SFIS system.
When working with PeopleSoft, it is important to understand the use of Effective Dates.
PeopleSoft’s effective-dating logic enables you to maintain an accurate history of information in the database. Effective dating allows you to store historical data, see changes in your data over time, and enter future data… When you enter new information that is related to existing data…, you do not want to lose or overwrite the data already stored in the database. To retain history, add a data row identified by the date when the information goes into effect: the effective date. You can use the information to look at what has happened up to now and plan for the future.
PeopleBooks goes on to explain the use of Action Options with Effective Dating – Update/Display, Correct History and Include History.
With Query, you can use dates in a variety of ways through expressions. You can find more information in the SFIS Blog on the Query – Expressions page. Among your options are:
- Derive information about specified fields between two dates – and include a prompt.
- Display any date in a field, including Effective Dates, in a desired format through the use of to_char in an expression.
- Examples would be the year, month-day, mm/dd/yyyy, etc., as a number or as words
- Similarly, time can be formatted as desired, for example, as AM or PM or military time
- Display today’s date in many formats with the use of expressions and Wes Functions (SYSDATE).
- Note that, unless you change the format, the SYSDATE will display as follows: 30-JUL-16
- Extract parts of a date in an expression through the use of substr (substring) which has this form: SUBSTR(DATEFIELD,X,Y). (Note that substr can be upper or lower case).
- Example: The date appears as 2016-05-30. SUBSTR counts from X position and returns Y characters. So for example –
- SUBSTR(DEGREE_DT,1,7) returns 2016-05
- substr(DEGREE_DT,3,8) returns 16-05-30
- Example: The date appears as 2016-05-30. SUBSTR counts from X position and returns Y characters. So for example –
- Return the Class Year with a Wes Function – this is based on the EMPLID
- In addition to Class Year, there are a number of variations on utilizing or returning dates through the use of Wes Functions, most notably SYSDATE (current date), but also application start date, current term, undergraduate term, first year inquiry term, admit term, program action date, and registration term being a few examples.
- Unique to Effective Dates:
- When you first join a record that has effective dating, you will get this message: An effective date criteria has been automatically added for this effective dated record (139,60).
- What is important to know when you see this message is that the database will automatically give you the very latest dated row for the criteria.
- Through Criteria Condition Types, a number of Effective Date situations can be shown.
- If necessary, for certain reasons, Effective Dates can be deleted from a query.
- Using Effective Dates in a subquery requires care.
- When doing table joins on effective dated information, you will rarely modify the criteria that PeopleSoft automatically creates. If you do need to change the criteria, be certain you’re changing the correct rows of criteria. Review the demonstration of Effective Dates with Multiple Tables on the Effective Dates in Query page.
- Translate tables are effective-dated, so you must select which effective date to use for them. For most tables, PeopleSoft Query defaults to the current date, meaning that it uses the currently active list of Translate table values. However, if the table you’re querying is also effective-dated, PS Query uses the value in the EFFDT field for a row. Read about Fields with Translate Values on the Query Tip Sheet Page.