PeopleSoft Date Function and Consideration

DateValue function to convert a date string and returns the result as a Date type
&myDate = DateValue("10/30/2012");

This is function is dependent on user's Date Format personalization setting, so if user Date Format personalization setting is set to DD/MM/YY the you will get error

Invalid date in function DateValue. (180,113)

You may use below function to avoid this issue, there are many other Date and time function documented in PeopleBook  that you can look but these are useful for you to have instant solution

·        Date


The Date function takes a number in the form YYYYMMDD and returns a corresponding Date value.

·        Date3

Date3(year, month, day)

The Date3 function accepts a date expressed as three integers: year, month, and day. It returns a corresponding Date value

·        DateTimeToLocalizedString

DateTimeToLocalizedString({datetime | date}, [Pattern])

Use the DateTimeToLocalizedString function to convert either datetime or date to a localized string

&String = ​DateTimeToLocalizedString(%Date, "MM/DD/YYYY");

Date, DateTime, and Time Wrappers
Use date or time wrappers (%Datein, %TimeOut, %Dateout, %currentdatein and so on) when selecting date or time columns into memory. Different database platforms use different internal formats for these data types.

select emplid, %dateout(effdt) from ps_job  a where emplid = :1 and a.effdt = %datein(:2)

You may use %CurrentDateIn instead of SYSDATE in SQL, for example

select emplid, %dateout(effdt) from ps_job  a where emplid = :1 and a.effdt < %CurrentDateIn

Change PeopleSoft System Date Format