Tip 032: Summary of Date Functions

PeopleCode | A ‘cheat sheet’ of all the date-related functions

A. Blind Dates

Even after 20 years of using App Designer, I still need to check PeopleBooks on a regular basis to remind myself of all the various date functions, many of which have very similar sounding names. Therefore, I’ve created this ‘cheat sheet’ to summarise the more important date functions. Although this list is mainly concerned with dates, I’ll briefly mention the related time (and date-time) functions.

B. PeopleCode

(a) %Date – to access the current system date

Local date &currentDate;
&currentDate = %Date;

[closely related: %Time, %DateTime]

(b) Date(number) – converts a number in the format of YYYYMMDD into a date

Local date &effDt;
&effDt = Date(20150101);

[closely related: Time(number)]

(c) Date3(year, month, day) – similar to the ‘Date’ function described above, except that the three parameters are passed in as separate year, month and day values.

Local date &effDt;
&effDt = Date3(2015,1,1);

[closely related: Time3(hours, minutes, seconds), DateTime6(years, months, days, hours, minutes, seconds)]

(d) DateValue(string) – converts a string into a date variable. The string must match the format as defined in user personalizations.

Local date &effDt;
&effDt = DateValue("2015-01-01");

If you’re not certain what the format is, set up a date variable and then display a message:

Local date &effDt;
&effDt = Date(20150101);
MessageBox(0, "", 0, 0, &effDt);

Of course the major problem with ‘DateValue’ is that it assumes that all users have the same date format. This may not be a problem on some sites, but is likely to be an issue with international sites.

[closely related: TimeValue(string), DateTimeValue(string)]

(e) Day(date) / Month(date) / Year(date) – converts the passed date into a numeric value representing the day, month or year. In the following example, the &month variable will be set to 6

Local date &effDt;
Local integer &month;
&effDt = DateValue("2015-06-01");
&month = Month(&effDt);

[closely related: Hour(time), Minute(time), Second(time)]

(f) DateTimetoLocalizedString – refer to Tip 023 for further details for this date-to-string conversion function.

C. SQL

(a) %CurrentDateIn – used for specifying the value of ‘today’ in a where clause, or as part of an ‘Insert’ statement.

select 'x'
from PS_RECORD_NAME
where EFFDT <= %CurrentDateIn;

[closely related: %CurrentTimeIn, %CurrentDateTimeIn]

(b) %CurrentDateOut – used for specifying the value of ‘today’ as part of a select statement.

select run_cntl_id, %CurrentDateOut as process_date
from PS_RECORD_NAME
where OPRID = :1;

[closely related: %CurrentTimeOut, %CurrentDateTimeOut]

select emplid
from PS_RECORD_NAME
where %DateDiff(start_date, end_date) > 365;

(d) %DateIn – used for converting a date field (or a hardcoded value) into the specific format required by the database. %DateIn is used for where clauses and as part of ‘Insert’ statements.

Select emplid
From PS_RECORD_NAME
Where effdt <= %DateIn(:1);

[closely related: %TimeIn, %DateTimeIn]

(e) %DateOut – similar to %DateIn, but used for select clauses only. Note that hard-coding a date parameter is not possible for %DateOut.

Select oprid, run_cntl_id, %DateOut(prcs_date)
From PS_RECORD_NAME;

[closely related: %TimeOut, %DateTimeOut]

D. SYSDATE (and Upsetting the Purists)

Finally, it’s worth making a quick point about the ‘SYSDATE’ construct. This is the Oracle database constant used to represent the current system date:

select sysdate
from dual;

PeopleSoft was originally developed with multiple database platforms in mind, so to avoid database specific language, most PeopleSoft code will steer clear of SYSDATE and use its own proprietary constructs instead.

There’s nothing especially wrong with SYSDATE however. A lot of developers write their SQL in an external editor and then just copy the end result directly into an PeopleTools object. These statements often use SYSDATE, but it’s generally not a major problem. The code will still work according to spec, which is always the primary goal of any developer. Furthermore, most PeopleSoft installations nowadays run on an Oracle database, and there is little chance of the underlying database being changed. However, be aware that there are some purists out there who get unduly upset upon seeing ‘SYSDATE’ statements liberally scattered about a piece of code.

<< Previous | Next >>