Using the FND_STANDARD_DATE valueset in Oracle Applications R12

Date parameters for concurrent programs in Oracle Applications R12 must be set up using the FND_STANDARD_DATE valueset.

The date value entered by the user at runtime is passed to the underlying program as a DATE-TIME STRING, not as a DATE.

This format of this VARCHAR2 data item is “YYYY/MM/DD HH24:MI:SS” (e.g. “2011/10/01 00:00:00”).

In order to use this VARCHAR2 value in a WHERE clause predicate, it can be converted to a DATE value using the fnd_date.canonical_to_date function, as shown in the following example:

SELECT user_name
FROM   fnd_user
WHERE  last_update_date >  fnd_date.canonical_to_date('2011/12/31 00:00:00')

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s