Manipulating Dates in PS Query

NOTE: This assumes your PeopleSoft installation is on an Oracle database.

When you need to manipulate a date field to be used as a field in PS Query, you must convert the date back to a date type (TO_DATE) and then to a character type (TO_CHAR). This is because PeopleSoft Query automatically converts the date to a character field when used as a field.

A date field used as a field prior to manipulation when viewing in View SQL:

• TO_CHAR(A.EFFDT,'YYYY-MM-DD')

To convert effective date to a year format, use Expressions and enter:

• TO_CHAR(TO_DATE(A.EFFDT,'YYYY-MM-DD'),'YYYY')
  

Ensure you have clicked Use as Field after saving the expression. Now view in View SQL, and you will see.

• TO_CHAR(TO_DATE(TO_CHAR(A.EFFDT,'YYYY-MM-DD'),'YYYY-MM_DD'),'YYYY-MM')

However, when manipulating a date to be used in the criteria section of your query you need only convert the date to a character (TO_CHAR).

A date field used in the criteria to manipulation when viewing in View SQL:

• A.EFFDT

To convert effective date to a year only format to be used in the critia, use Expressions and enter:

• TO_CHAR(A.EFFDT,'YYYY')
  

Now view in View SQL, and you will see.

• TO_CHAR(A.EFFDT,'YYYY')

SQL Example:

SELECT A.EMPLID, TO_CHAR(TO_DATE( TO_CHAR(A.EFFDT,'YYYY-MM-DD'),'YYYY-MM-DD'),'YYYY')
FROM PS_NAMES A
WHERE (TO_CHAR(A.EFFDT,'YYYY') = '2008')

A little crazy, I know, but that's how it is