PT 8.6-
Randall Groncki

Introduction

PeopleSoft as an ERP system is driven by dates. When we see those dates in our queries, it naturally raises questions:
• What is the difference between this and another date?
• How long ago was that date?
• What was the date X days before or after that date?

PSQuery and dates

PSQuery is a great tool for PeopleSoft ad hoc reporting. But it does have a challenge when it comes to working with Dates. Copying valid date math and comparisons from a SQL tool will most likely result in PSQuery errors.

Why?

PSQuery converts all dates to character strings before we have a chance to act on the date fields as dates. What we see as a date field in the field list or “Where” clause has already been converted to a string before our formulas are applied.

All is not lost!

PeopleSoft provides Meta-SQL elements enabling us to manipulate data such as dates in our SQL statements to include PSQuery. Meta-SQL are platform independent functions that expand at runtime to platform specific syntax. A valid Meta-SQL expression will work the same in DB2, SQLServer or Oracle despite the fact that the platform specific syntax is different in these databases.

All is not lost!

PeopleSoft provides Meta-SQL elements enabling us to manipulate data such as dates in our SQL statements to include PSQuery. Meta-SQL are platform independent functions that expand at runtime to platform specific syntax. A valid Meta-SQL expression will work the same in DB2, SQLServer or Oracle despite the fact that the platform specific syntax is different in these databases.

PeopleBooks Reference: Meta-SQL

Refer to the PeopleBooks for your specific PeopleTools platform.

PeopleTools 8.60 Meta-SQL Reference

This document’s scope is limited to Date Meta-SQL. However, there many more Meta-SQL commands enabling functionality other than dates.

Meta-SQL Date Functions in PSQuery

Today

%CurrentDateIn

The way to express the current date as of execution is %CurrentDateIn. This will expand to the platform specific current date function of your database such as SYSDATE in Oracle.

Now

%CurrentDateTimeIn

If you need to know the Now, down to the hour and minute, use the %CurrentDateTimeIn Meta-SQL function. This resolves to SYSTIMESTAMP on the Oracle Database.

Remember to se the Expression Type to “DateTime”

Date Wrapper

%DateIn()

Before we can use other Meta-SQL functions like %DateDiff() and %DateAdd(), we need to “wrap” that date field in %DateIn() function.

Remember, PSQuery converts all dates to character strings. This %DateIn() function removes that character conversion so the field back to being a date.

Example: Use the EFFDT in a function:

 %DateIn(A.EFFDT)

Depending on the expression, you will probably choose an Expression Type of Date or Number

DateTime Wrapper

%DateTimeIn()

Just like a date, we need to wrap our DateTime fields in the %DateTimeIn()wrapper to use the field in other Meta-SQL functions.

Example: Use the LASTUPDDTTM field in a MetaSQL function:

%DateTimeIn(A.LASTUPDDTTM)

Difference in days between two dates

%DateDiff()

The %DateDiff() Meta-SQL function returns the number of days between two dates. This function returns a number, not a date.

Example: how many days between an employee’s last hire date and the Job EFFDT

%DateDiff(%datein(A.LAST_HIRE_DT), %datein(A.EFFDT))

Both of the date fields are wrapped in the %DateIn() function before we compare them with the %DateDiff() function.

The first date in the %DateIn() function is the earlier date. The Second date is the later date. It might help to imaging the two dates on a horizontal time line read from left to right. The resulting number of days is the days between those two dates.

*Note: The %DateDiff() function will work if you enter the dates in the wrong order. But the answer will be a negative number.

Adding days to dates

%DateAdd

Another common requirement is to know the date that is a number of days before or after our date field. Use the %DateAdd() Meta-SQL function to find that new date.

This function returns a new Date. Ensure the Expression Type is set to Date.

Example 1: What date is 6 months AFTER the EFFDT?

%dateAdd(%datein(A.EFFDT), 183)

Example 2: What is the date 6 months prior to the current date?

%dateAdd(%CurrentDateIn, -183)

Note: Notice we’re adding a negative 183 (-183) to subtract days from the current date.

Difference in minutes between two datetimes

%DateTimeDiff()

The %DateTimeDiff function is similar to the %DateDiff() function, but uses DateTime fields instead of date fields. The function returns the number of minutes between the two fields.

Ensure your Expression Type is a set to Number and the length large enough to show the size of the minutes results you are expecting.

Example: How long ago is the LASTUPDDTTM field value?

%DateTimeDiff(%DateTimeIn(A.LASTUPDDTTM ), %CurrentDateTimeIn)

Notice the A.LASTUPDDTTM is wrapped in the %DateTimeIn() function before it is given to the %DateTimeDiff() function.

Converting a datetime to a date

%DatePart(DTTM_Column)

Date and DateTime fields are not interchangeable. Putting the wrong type of field into one of the wrappers will generate a PSQuery error during execution.

Before comparing, we first must convert the DateTime field to a Date field using the %DatePart()function.

Example: %DatePart(%DateTimeIn(A.LASTUPDDTTM))returns just the date portion of the LASTUPDDTTM field.

Notice how we wrapped LASTUPDDTTM in the %DateTimeIn() function Before giving it to the %DatePart() function.

In this example, we want to see the number of days between the LastUpDTTM field and Today.

%DateDiff( %DatePart(%DateTimeIn(A.LASTUPDDTTM)),%CurrentDateIn)

Meta-SQL Expressions in the Where Clause

We can use Expressions with Meta-SQL in our where clauses and criteria just as easily as using them as a field.

For example, we want PSQUery to return all current job rows with an EFFDT in the past year.

the expression %dateadd(%currentdatein, -365) will return a date 1 year prior to the current date.

Using that in criteria: