Dates in PS 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.

More About Functions

For more information about functions in PeopleSoft Query and some other interesting stuff too, read our eBook: Using Functions in PeopleSoft Query