Q: How do I use dates and date functions in PeopleSoft Query?

PeopleSoft Query surrounds dates and datetimes from the database with a conversion to consistent format. This is similar to the effect of the PeopleCode %DateOut, %TimeOut and %DateTimeOut. The effect of this can be seen when you preview the SQL.

The date functions are particular to the database in use. For instance in Oracle:

to_char(A.EFFDT,'YYYY-MM-DD')

The conversion to char means that the date functions will not work natively. You can convert this back to date by creating an expression as follows:

to_date(A.EFFDT,'YYYY-MM-DD')

This means that you can then use date functions:

add_months(to_date(A.EFFDT,'YYYY-MM-DD'),-12)

Note that DateTimes can be converted differently and so care is needed when mixing data types.

Computing Articles