APEX

I would like to document some issues I had with the HOWTO document called "Work with Dates".

http://www.oracle.com/technology/products/database/htmldb/howtos/howto_handle_dates.html

The first issue is in regards to the section called "Using an Application Level Format Mask to Customise a Date Picker". In the example, the user wants to change the date format in input/output screens to the Date format of "YYYY-MM-DD" (also known as the Canadian National Standard Date format or the ISO Standard Date format). In the example, a substitution string is created and a substitution value of “YYYY-MM-DD’ is assigned (this is done at the Application Level under the Application Tab and then Edit Attributes and then Substitution Strings). The example then shows that when the Date Picker is used in the form, the date returned is in the YYYY-MM-DD format. What the document did not say “specifically” was that for this to work, the Substitution String has to be called “PICK_DATE_FORMAT_MASK” exactly. If you call it something else or have a spelling error in the Substitution String Name, this will not work. The substitution String must be named exactly as “PICK_DATE_FORMAT_MASK”. I believe a little clarification of this fact will save individuals a great deal of time, in the future.

Note that this change only affects those Date Pickers that are specify as “Date Format of “Date Picker (use application format mask)”.

The other problem falls under the “Changing the Date Format in a Form (Using a Date Picker)” section. In this section, it leads one to believe that if you have a date in one format and want to display it in another format, you simply assign a FORMAT Mask at the Item’s Source Level. This does not work.

For example, if a call to the database (SQL or PL/SQL) returns a date in the format of DD-MON-YYYY, one cannot simply enter a value of YYYY-MM-DD in the FORMAT section of the Item’s Source to have the date somehow magically converted to the YYYY-MM-DD format. If you have created an entry for the PICK_DATE_FORMAT_MASK and set it to YYYY-MM-DD and then use the “Date Picker (use application format mask)” for a Date Picker and set the FORMAT at the Item Level to “YYYY-MM-DD” and retrieve a row from the database, you will get the following error:

Unable to cast value "11-JUN-1921" to date format mask PICK_DATE_USING_FLOW_FORMAT_MASK

The work-around for this is to forgo setting the FORMAT Mask at the Item level, as doing so accomplishes nothing. You want to define the format for your PICK_DATE_FORMAT_MASK at the application level and then set your date fields to “Date Picker (use application format mask)” and then before you perform any database I/O (SELECT,INSERT,UPDATE,DELETE or calls to Stored Procedures) issue an

EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD''' ;

And set the NLS_DATE_FORMAT to the format of you Date Picker (the String Value you assigned to the Substitution String = PICK_DATE_FORMAT_MASK). This will cause the date to be in the same format of the Date Picker.