FND_STANDARD_DATE Oracle Apps Scripts

Oracle Developer > Scripts > Using FND_STANDARD_DATE Value Set in Oracle Apps

When defining dates for Flex fields, the value set is important. If you plan to view and manage the field from the form, then you should probably use FND_STANDARD_DATE. But if you are building a database application that accesses this field from the back-end, you will need to be mindful that you convert the flex field value appropriately.

Possible Symptoms of a date conversion error are:
ORA-01861:literal does not match format string.
ORA-01843: not a valid month

FND_STANDARD_DATE from the back-end is YYYY/MM/DD HH24:MI:SS while the Oracle database native is DD-MON-YYYY

Therefore if you tried to do a select to_date('20-SEP-2007') from dual, it works fine.

But if you try to do a select to_date('2007/09/20 12:01:02') from dual, you will get the following error -> ORA-01861: literal does not match format string

Make sure you do the type conversion when accessing fields from Oracle apps tables that are FND_STANDARD_DATE to avoid this error.

Alternatively, if this flex field is only for back end use and will not be displayed, you can use the value set of "Date" to avoid doing type conversions.